### ===================================================================================================================
### Create VIIA BKG report, form for the scopechanges
### ===================================================================================================================
# Copyright ©VIIA 2024
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
import pytz
from pathlib import Path
from datetime import datetime
from typing import TYPE_CHECKING, Optional, Dict, Union, List
# 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_opdracht_types
# Import module openpyxl
# This module is used for creating Excel-sheet for the project-leader
from openpyxl import load_workbook
from openpyxl.utils.cell import get_column_letter
### ===================================================================================================================
### 2. Function to create the BKG report
### ===================================================================================================================
[docs]def viia_create_bkg_report(
project: ViiaProject, scope_change_id: int, template_locations: Dict[str, str],
output_folder: Optional[Path] = None) -> List[Path]:
"""
This function creates a BKG report for a specific object-assignment scope-change. 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!
Output:
- Filled BKG 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('BKG Report', ref_folder=project.workfolder_location)
report_location = project.workfolder_location / 'BKG 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
data = collect_data_from_myviia(project=project, scope_change_id=scope_change_id)
# Map excel column name and data key
column_mapping = {
'adres_id': 'ADRESID',
'dossiernummer': 'Dossiernummer',
'herbeoordeling_dossiernummer': 'Herbeoordeling dossiernummer',
'adres': 'Adres',
'erfgoed': 'Erfgoed',
'opdrachtnummer': 'Opdrachtnummer',
'type_opdracht': 'Type opdracht',
'bedrag_excl_btw': 'Bedrag excl. BTW',
'reden_meerwerk': 'Reden Meerwerk'}
# Create report(s)
generated_reports = []
for report_name, template_location in template_locations.items():
try:
cet_time = datetime.now(pytz.timezone('CET'))
time_reference = datetime.now().strftime('%Y%m%d%H%M%S')
output_document_name = \
f"Meerwerk_BKG_{project.project_information['objectnummer_viia']}_{data['opdrachtnummer']}_" \
f"{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)
ws = wb.active
table = ws.tables["Tabel1"]
# Fill in header
ws['B4'] = data['opdrachtnummer']
ws['B5'] = datetime.now()
# Write data for selected contract to the worksheet
row_pointer = 15
for k, v in data.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
# 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, scope_change_id: int) -> Dict[str, Union[str, float]]:
"""
This function collects scope change data that has been retrieved from MYVIIA and stored in the project variable.
This data is to be used to fill the BKG report.
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.
Output:
- Returns dictionary with the required information for the BKG form.
"""
# Get object details from MYVIIA
address_id = []
address = []
dossier_nrs = []
rerun_dossier_nrs = []
for address_obj in project.project_information['object_adressen']:
if address_obj['adres_id_ncg']:
address_id.append(address_obj['adres_id_ncg'])
address.append(address_obj['straatnaam'] + ' ' + address_obj['huisnummer'] + ' ' + address_obj['plaatsnaam'])
if address_obj['dossiernummer_og'] is None:
dossier_nrs.append("ONBEKEND DOSSIERNUMMER")
else:
dossier_nrs.append(address_obj['dossiernummer_og'].split('/')[0].replace(' ', ''))
if len(address_obj['dossiernummer_og'].split('/')) > 1:
rerun_dossier_nrs.append(', '.join(address_obj['dossiernummer_og'].replace(' ', '').split('/')[1:]))
heritage = project.project_information['erfgoed_status']
# Get object-opdracht-scopewijziging info
scope_change = [
sc['scopewijziging'] for sc in project.project_information['object_opdracht_scopewijzigingen']
if sc['scopewijziging_id'] == scope_change_id][0]
contract = [
object_opdracht['opdracht'] for object_opdracht in project.project_information['object_opdrachten']
if object_opdracht['opdracht_id'] == scope_change['opdracht_id']][0]
contract_nr = contract['opdrachtnummer_og']
contract_types = myviia_get_opdracht_types(token=project.token)
contract_type = [
opdracht_type['opdracht_type'] for opdracht_type in contract_types
if opdracht_type['id'] == contract['opdracht_type_id']][0]
financial_impact = scope_change['financieel_impact']
reason_additional_work = scope_change['wijziging_omschrijving']
# Collect all data
scope_change_data = {
'adres_id': ', '.join(address_id),
'dossiernummer': ', '.join(dossier_nrs),
'herbeoordeling_dossiernummer': ', '.join(rerun_dossier_nrs),
'adres': ', '.join(address),
'erfgoed': heritage,
'opdrachtnummer': contract_nr,
'type_opdracht': contract_type,
'bedrag_excl_btw': float(financial_impact),
'reden_meerwerk': reason_additional_work}
# Return the collected data
return scope_change_data
### ===================================================================================================================
### 4. End of script
### ===================================================================================================================