### ===================================================================================================================
### VIIA create CRM deliverable
### ===================================================================================================================
# Copyright ©VIIA 2024
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
from typing import TYPE_CHECKING, Dict, List
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
from viiapackage.database import myviia_get_cost_estimation, myviia_get_cost, myviia_login
# Import module openpyxl
# This module is used for creating Excel sheet for CRM deliverable
from openpyxl import load_workbook
### ===================================================================================================================
### 2. Function to create CRM deliverable
### ===================================================================================================================
[docs]def viia_create_crm(
project: ViiaProject, template_locations: Dict[str, str], output_folder: Path = None) -> List[Path]:
"""
This function creates the CRM deliverable 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
marked unknown (NL: onbekend).
- 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 reports
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}-{report_name.upper()}_{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()
engineering_measures = myviia_get_eng_measure(object_deel_id=object_deel_id, token=project.token)
cost_estimation = myviia_get_cost_estimation(object_deel_id=object_deel_id, token=project.token)
cost_key_figures = myviia_get_cost(object_deel_id=object_deel_id, token=project.token)
# Green Part
data = dict()
data['Ingenieursbureau'] = 'VIIA'
data['Naam_TVA'] = f'VIIA-{project.name}-TVA.docx'
d = datetime.now()
data['Datum opstellen TVA'] = f'{d.day}-{d.month}-{d.year}'
object_address = project.project_information.get('object_adressen', [{}])[0]
street_name = object_address.get('straatnaam', 'onbekende straat')
house_number = object_address.get('huisnummer', 'x')
data['Adres'] = ' '.join([street_name, house_number])
data['Plaats'] = object_address.get('plaatsnaam', 'onbekende plaats')
# Yellow part
data['Tijdvak'] = project.project_information.get('reference_period', 'onbekend').upper()
data['PGA'] = project.project_information.get('pga', 'onbekend')
object_cluster = project.project_information.get('object_cluster_id', {})
type_1 = object_cluster.get('cluster', 'onbekend')
data['Type 1'] = f'[{type_1}]'
data['Type 2'] = object_cluster.get('typologie', 'onbekend')
data['Bouwjr'] = project.project_information.get('oorspronkelijk_bouwjaar', 'onbekend')
data['Berekening'] = project.project_information.get('analysis_type', 'onbekend')
# The engineering software can't be automatically detected yet
# data['Programma'] = 'Diana'
conclusion_analysis = project.project_information.get('conclusie_analyse', '').lower()
if conclusion_analysis == 'te bepalen':
data['Norm'] = 'bsc/tva onvoltooid'
elif conclusion_analysis == 'op norm':
data['Norm'] = 'Op norm'
elif conclusion_analysis == 'versterking benodigd':
data['Norm'] = 'Niet op norm'
elif conclusion_analysis == 'nader onderzoek':
data['Norm'] = 'Aanvullend onderzoek noodzakelijk'
# Orange part
if not cost_estimation:
data['Kosten'] = 'onbekend'
else:
# Get most recent cost estimation
latest_cost_estimation = \
sorted(cost_estimation, key=lambda y: datetime.strptime(y['date'], '%Y-%m-%d'))[-1]
for entry in ['date', 'id', 'resource_id']:
latest_cost_estimation.pop(entry)
data['Kosten'] = sum([val for val in latest_cost_estimation.values() if val is not None])
if not cost_key_figures:
data['BVO'] = 'onbekend'
else:
# Get most recent cost key figures
recent_cost_key_figures = \
sorted(cost_key_figures, key=lambda y: datetime.strptime(y['date'], '%Y-%m-%d'))[-1]
data['BVO'] = recent_cost_key_figures.get('bvo', '')
if data['Kosten'] == 'onbekend' or data['BVO'] == 'onbekend':
data['Kosten/BVO'] = 'onbekend'
else:
data['Kosten/BVO'] = data['Kosten'] / data['BVO'] if data['BVO'] != 0 else 0
# Measures
for measure in engineering_measures:
measure_name = measure.get('name', None)
if not measure_name:
continue
dimension = measure.get('dimension', '-')
data[measure_name] = dimension
# Writing to excel
wb = load_workbook(project.viia_settings.project_specific_package_location / template_location)
ws = wb.active
for key, value in data.items():
target_column = column_mapping_dict.get(key)
if not target_column:
continue
target_row = '5'
ws[target_column + target_row] = value
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. Column mapping for Excel template
### ===================================================================================================================
column_mapping_dict = {
'Ingenieursbureau': 'A',
'Naam_TVA': 'B',
'Datum opstellen TVA': 'C',
'Adres': 'E',
'Plaats': 'F',
'Tijdvak': 'G',
'PGA': 'H',
'Type 1': 'I',
'Type 2': 'J',
'Bouwjr': 'K',
'Berekening': 'L',
'Programma': 'M',
'Norm': 'N',
'Kosten': 'O',
'BVO': 'P',
'Kosten/BVO': 'Q',
'L1-A': 'R',
'L1-B': 'S',
'L1-C': 'T',
'L1-D': 'U',
'L1-E': 'V',
'L2-001': 'W',
'L2-005': 'X',
'L2-011': 'Y',
'L2-013': 'Z',
'L2-014': 'AA',
'L2-015': 'AB',
'L2-025': 'AC',
'L2-26': 'AD',
'L2-30': 'AE',
'L2-033': 'AF',
'L2-043': 'AG',
'L2-044': 'AH',
'L2-045': 'AI',
'L2-049': 'AJ',
'L2-053': 'AK',
'L2-054': 'AL',
'L2-55': 'AM',
'L2-58': 'AN',
'L2-AG': 'AQ',
'L3-A': 'AR',
'L3-B': 'AS',
'L3-D': 'AT',
'L3-E': 'AU',
'L3-G': 'AV',
'L3-H': 'AW',
'L3-L': 'AX',
'L3-M': 'AY',
'L3-N': 'AZ',
'L4-D': 'BB',
'L4-E': 'BC',
'L4-F': 'BD',
'L4-G': 'BE',
'L4-H': 'BF',
'L4-I': 'BG',
'L4-K': 'BH',
'L4-O': 'BI',
'L4-P': 'BJ',
'L4-Q': 'BK',
'L5-A': 'BL',
'L5-B': 'BM',
'L5-D': 'BN',
'L5-K': 'BO',
'L5-N': 'BP',
'L5-P': 'BQ',
'L5-T': 'BR',
'L5-V5': 'BS',
'L5-X': 'BT',
'L5-BB': 'BU',
'L5-DD': 'BV',
'L5-...': 'BW',
'L6-A': 'BX',
'L6-B': 'BY',
'L6-C': 'BZ',
'L6-D': 'CA',
'L6-I': 'CB',
'L6-J': 'CC',
'L7': 'CD'}
### ===================================================================================================================
### 4. End of script
### ===================================================================================================================