### ===================================================================================================================
### Create VIIA cost report
### ===================================================================================================================
# Copyright ©VIIA 2024
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
from typing import TYPE_CHECKING, List, Dict
import re
from pathlib import Path
from datetime import datetime
# 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.viia_myviia_get_info import myviia_get_eng_measure, myviia_get_user_info
from viiapackage.database import myviia_get_cost, myviia_login
# Import module openpyxl
# This module is used for creating Excel-sheet for cost engineer
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import quote_sheetname
from openpyxl.worksheet.datavalidation import DataValidation
### ===================================================================================================================
### 2. Function to create cost estimation report
### ===================================================================================================================
[docs]def viia_create_cost_report(
project: ViiaProject, template_locations: Dict[str, str], output_folder: Path = None) -> List[Path]:
"""
This function creates the cost report for an object. Relevant data is assembled from project_information,
cost estimation and cost key figures. Also, the applied measures are retrieved from MYVIIA from engineering
database.
Input:
- 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('ER', ref_folder=project.workfolder_location)
report_location = project.workfolder_location / 'ER'
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.")
# Create report(s)
generated_reports = []
for report_name, template_location in template_locations.items():
try:
time_reference = datetime.now().strftime('%Y%m%d%H%M%S')
output_document_name = f"VIIA-{project.name}-Overzicht_Kosten_{time_reference}.xlsx"
target_file = report_location / output_document_name
object_deel_id = project.get_myviia_object_deel_id()
# Check if token is already present in project
if not project.token:
# Login to MYVIIA webtool for multiple requests
project.token = myviia_login()
user_info = myviia_get_user_info(token=project.token)
cost_key_figures = myviia_get_cost(object_deel_id=object_deel_id, token=project.token)
engineering_measures = myviia_get_eng_measure(object_deel_id=object_deel_id, token=project.token)
# Collect addresses
addresses = list(set([address['straatnaam'] for address in project.project_information['object_adressen']]))
address_strings = []
for address in addresses:
numbers = [
adr['huisnummer'] for adr in project.project_information['object_adressen']
if adr['straatnaam'] == address]
try:
numbers = sorted(numbers)
except TypeError:
pass
address_strings.append(f"{address} {', '.join(numbers)}")
address = ', '.join(address_strings)
# Check function of the building
building_category = re.sub(r'[0-9]', '', project.project_information['objectnummer_viia'])
if len(building_category) <= 2:
building_function = building_functions.get(building_category[0])
else:
building_function = None
# Check if barn (NL: loods)
loods = 'Nee'
if 'schuur' in project.project_information['objectdeel'].lower():
loods = 'Ja'
if building_function and building_function.lower() == 'l':
loods = 'Ja'
# Get the data for the estimated cost of the strengthening measures
data = dict()
object_part = ''
if project.project_information['objectdeel'] != 'Gehele object':
object_part = project.project_information['objectdeel']
data['objectnaam'] = f"VIIA_{project.project_information['objectnummer_viia']}"
data['objectdeel'] = object_part
data['straat_huisnummer'] = address
data['postcode'] = project.project_information['object_adressen'][0]['postcode']
data['plaats'] = project.project_information['object_adressen'][0]['plaatsnaam']
data['gemeente'] = project.project_information['gemeente_id']['naam']
data['gemeente_code'] = project.project_information['gemeente_id']['code']
data['gevolgklasse'] = consequence_class_dict.get(project.project_information['gevolgklasse'], '-')
data['oorspronkelijk_bouwjaar'] = project.project_information['oorspronkelijk_bouwjaar']
data['aantal_won_in_project'] = str(len(project.project_information['object_adressen']))
data['monument_type'] = monument_types.get(project.project_information['erfgoed_status'])
data['monument_status'] = 'Ja' if data['monument_type'] else 'Nee'
data['other_monument_type'] = other_monument_types.get(project.project_information['erfgoed_status'])
data['other_monument_status'] = 'Ja' if data['other_monument_type'] else 'Nee'
data['is_schuur_garage_loods'] = loods
data['internal_project_number'] = project.project_information['objectnummer_viia']
data['projectnummer_NCG'] = project.project_information['opdracht_id']['kenmerk_og']
data['dossiernummer_NCG'] = \
', '.join([address['dossiernummer_og'] for address in project.project_information['object_adressen']])
# data['vraagspecificatie'] = project.project_information['opdracht_id']['vraagspecificatie']
data['version'] = '01'
data['company_name'] = 'VIIA'
data['initial_downloader'] = user_info['initialen']
data['building_function'] = building_function
data['calculation_method'] = project.project_information['analysis_type']
data['npr_version'] = project.project_information['npr_versie']
# Gather data for cost figures
if cost_key_figures:
data['bvo'] = cost_key_figures[-1].get('bvo')
data['bgi'] = cost_key_figures[-1].get('bgi')
data['bbo'] = cost_key_figures[-1].get('bbo')
data['bto'] = cost_key_figures[-1].get('bto')
data['bgo'] = cost_key_figures[-1].get('bgo')
data['ogo'] = cost_key_figures[-1].get('ogo')
data['bdo'] = cost_key_figures[-1].get('bdo')
data['odo'] = cost_key_figures[-1].get('odo')
# Writing to Excel-file
wb = load_workbook(project.viia_settings.project_specific_package_location / template_location)
ws = wb['Kosten versterking']
# Gather all the measures and corresponding rows,
# use data only in this case to avoid the reference formula text
wb_data_only = load_workbook(project.viia_settings.project_specific_package_location / template_location,
data_only=True)
ws_data_only = wb_data_only['Kosten versterking']
measures_template_data = \
{ws_data_only[f'D{row}'].value: str(row) for row in range(74, 358) if
isinstance(ws_data_only[f'D{row}'].value, str) and re.search("L[0-9]", ws_data_only[f'D{row}'].value)}
wb_data_only.close()
for key, value in data.items():
target_row = row_mapping_dict.get(key)
if not target_row:
continue
# Check the monument columns
if key in ['monument_type', 'other_monument_type', 'objectdeel']:
target_cell = f'H{target_row}'
# Fill in the other information
else:
target_cell = f'F{target_row}'
ws[target_cell].value = value
# Check measures and fill in
not_present = []
for measure in engineering_measures:
if measures_template_data.get(measure['name']):
ws[f"J{measures_template_data[measure['name']]}"].value = measure['dimension']
else:
not_present.append(measure)
if not_present:
red = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
warning_string = 'WAARSCHUWING: Maatregelen onbekend: '
warning_string += \
', '.join([f"{measure['name']} ({measure['dimension']})" for measure in not_present])
ws['N21'].value = warning_string
ws['N21'].fill = red
# Keep the dropdown options of the cells
if key in dropdown_dict.keys():
ws[target_cell].value = value if value else 'Maak keuze'
dv = DataValidation(
type='list', formula1=dropdown_dict[key]['formula'].format(
quote_sheetname(dropdown_dict[key]['sheetname'])))
ws.add_data_validation(dv)
dv.add(ws[target_cell])
# 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. Mapping for Excel template
### ===================================================================================================================
row_mapping_dict = {
'objectnaam': '13',
'objectdeel': '13',
'straat_huisnummer': '14',
'postcode': '15',
'plaats': '16',
'gemeente': '17',
'gevolgklasse': '18',
'oorspronkelijk_bouwjaar': '19',
'aantal_won_in_project': '20',
'monument_status': '21',
'monument_type': '22',
'other_monument_status': '22',
'other_monument_type': '22',
'is_schuur_garage_loods': '24',
'internal_project_number': '25',
'projectnummer_NCG': '26',
'dossiernummer_NCG': '27',
'version': '37',
'company_name': '39',
'initial_downloader': '40',
'building_function': '43',
'calculation_method': '44',
'npr_version': '45',
'bvo': '48',
'bgi': '49',
'bbo': '50',
'bto': '51',
'bgo': '53',
'ogo': '54',
'bdo': '56',
'odo': '57'}
consequence_class_dict = {
'CC1b': 'CC1b',
'CC2': 'CC2 - CC3',
'CC3': 'CC2 - CC3'}
monument_types = {
'Gemeentelijk monument': 'Gemeentelijkmonument',
'Rijksmonument': 'Rijksmonument'}
other_monument_types = {
'Karakteristiek pand': 'Karakteristiek object ',
'Beeldbepalend gebouw ': 'Beeldbepalend object ',
'Beschermd dorpsgezicht': 'Beschermd dorps of stadsgezicht',
'Beschermd stadsgezicht': 'Beschermd dorps of stadsgezicht'}
dropdown_dict = {
'gevolgklasse': {'formula': '{0}!$B$18:$B$21', 'sheetname': 'Tabellen'},
'monument_status': {'formula': '{0}!$B$4:$B$6', 'sheetname': 'Tabellen'},
'monument_type': {'formula': '{0}!$P$21:$P$27', 'sheetname': 'Tabellen'},
'other_monument_status': {'formula': '{0}!$B$4:$B$6', 'sheetname': 'Tabellen'},
'other_monument_type': {'formula': '{0}!$P$31:$P$35', 'sheetname': 'Tabellen'},
'is_schuur_garage_loods': {'formula': '{0}!$B$4:$B$6', 'sheetname': 'Tabellen'},
'building_function': {'formula': '{0}!$F$3:$F$20', 'sheetname': 'Tabellen'},
'calculation_method': {'formula': '{0}!$P$10:$P$17', 'sheetname': 'Tabellen'},
'npr_version': {'formula': '{0}!$P$3:$P$8', 'sheetname': 'Tabellen'}}
building_functions = {
'A': 'A. Agrarische objecten (boerderijen)',
'B': 'B. Bedrijfsgebouwen (grote bedrijfspanden en fabrieken)',
'G': 'G. Gemeentelijke huisvesting',
'H': 'H. Horeca',
'L': 'L. Loodsen (industriële objecten)',
'K': 'K. Kinderdagverblijven & BSO',
'M': 'M. MKB (midden & kleinbedrijf)',
'R': 'R. Recreactie en sport',
'P': 'P. Posten (politie, brandweer, ambulance, hulpverlening)',
'S': 'S. Schoolgebouwen',
'T': 'T. Thuis (vrijstaande woningen)',
'W': 'W. Wijkgebouwen en Dorpshuizen',
'X': 'X. Overige publieke gebouwen (kerken, musea)',
'Z': 'Z. Zorggebouwen'}
### ===================================================================================================================
### 4. End of script
### ===================================================================================================================