### ===================================================================================================================
### Report preselection in excel-sheet
### ===================================================================================================================
# Copyright ©VIIA 2024
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
import pytz
from datetime import datetime
from typing import TYPE_CHECKING, List, Optional
from pathlib import Path
from PIL.Image import Image as PIL_Image
from openpyxl import load_workbook
from openpyxl.utils.cell import get_column_letter
from openpyxl.styles import PatternFill, Alignment
from openpyxl.drawing.image import Image as openpyxl_Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
# 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.reference_approach.viia_convert_data_eng_database import MYVIIAEngDatabase
from viiapackage.reference_approach.viia_current_object import CurrentObject
### ===================================================================================================================
### 2. Function to report the preselection
### ===================================================================================================================
[docs]def viia_report_preselection(
project: ViiaProject, reference_objects: List[MYVIIAEngDatabase], current_object: CurrentObject,
extend_object_data: int, output_folder: Optional[Path] = None) -> Path:
"""
This function creates an Excel-sheet of possible reference objects, to be used in the Reference Approach.
Several properties such as the consequence class, building height, applied measures and overview pictures are shown
of these objects. This list of objects is ranked and ordered based on their similarity to the current object the
engineer is working on.
Input:
- project (obj): VIIA project object containing collections of fem objects and project variables.
- reference_objects (list of obj): List of instances of MYVIIAEngDatabase with references valid to be used in
the reference approach.
- current_object (obj): Object reference of the current object, for which the available data will be added in
the template.
- extend_object_data (int): Input for defining the amount of (best-fitting) objects that get
extended information. Extended information includes measures, cost estimate and overview pictures.
- 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:
- Excel sheet with the detailed data of the ranked reference objects for the user as a pre-selection to be used
further in the process of the reference approach.
"""
# Collect template file
template_location = \
project.viia_settings.project_specific_package_location / 'reference_approach' / 'templates' / \
'VIIA_Template_Preselection_20230530.xlsx'
if not template_location.exists():
raise FileNotFoundError(
f"ERROR: Preselection template file could not be found in expected location "
f"'{template_location.as_posix()}'. Please provide it manually to generate this report.")
# Create or check the location where the report is to be generated
if output_folder is None:
# Create subfolder
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.")
# Map excel column name and data key
column_mapping = {
'objectnumber_viia': 'Objectnr VIIA',
'objectpart': 'Objectpart',
'score': 'Score',
'score_cluster': 'Score cluster',
'score_construction_year': 'Score construction year',
'score_material_load_walls': 'Score material loadbearing walls',
'score_most_floor_height': 'Score most floor height',
'score_most_thickness_load_walls': 'Score thickness loadbearing walls',
'score_nr_levels': 'Score nr levels',
'score_other_floors_material': 'Score other floor material',
'score_pga': 'Score PGA',
'importance_factor': 'Importance factor',
'pga': 'PGA',
'design_pga': 'PGA design',
'cluster': 'Cluster',
'construction_year': 'Construction year',
'consequence_class': 'Consequence class',
'nr_levels': 'Nr levels',
'building_height': 'Building height',
'most_floor_height': 'Most floor height',
'most_thickness_load_walls': 'Most thickness loadbearing walls',
'material_load_walls': 'Material loadbearing walls',
'bg_floor_material': 'Ground floor material',
'other_floors_material': 'Other floors material',
'roof_material': 'Roof material',
'measures': 'Applied measures',
'total_cost': 'Total cost estimation',
'model_image': 'FEM Model',
'wall_image': 'Ground floor walls',
'floor_image': 'First floor',
'roof_image': 'Roof',
'box_link': 'Box Link',
'dat_file_link': 'Dat file link'}
# 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_{project.project_information['objectnummer_viia']}_Preselection_{time_reference}.xlsx"
target_file = report_location / output_document_name
fill_colour = PatternFill(start_color='FABF8F', end_color='FABF8F', fill_type='solid')
# Writing to excel
wb = load_workbook(template_location)
ws = wb['Preselection']
# Fill in header
ws['E2'] = project.project_information['objectnummer_viia']
ws['E3'] = f"{project.project_information['objectdeel']} {project.project_information['analysis_subtype']}"
ws['E4'] = datetime.now()
# Collect table object
table = ws.tables['Preselection']
# Set up table size
top_left_cell = 'A5'
bottom_right_cell = 'AG' + str(5 + len(reference_objects) + 2)
table.ref = top_left_cell + ':' + bottom_right_cell
ws.move_range('A7:A7', rows=len(reference_objects) - 1)
# Add data for the current object and fill in template
row_pointer = 6
for k, column_name in column_mapping.items():
if column_name in table.column_names and hasattr(current_object, k):
value = getattr(current_object, k)
if isinstance(value, list):
value = ', '.join(value)
if k == 'score' and value:
value = value / 100
elif k == 'most_thickness_load_walls' and value:
value = value * 1E3
elif k == 'consequence_class' and value:
value = value.lower().replace('cc', 'CC')
if value is None:
value = '-'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) + str(row_pointer)] = value
else:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) + str(row_pointer)] = '-'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) + str(row_pointer)].fill = fill_colour
# Loop over data entries of the reference objects and fill in template
for data_entry in reference_objects:
row_pointer += 1
for k, column_name in column_mapping.items():
if (row_pointer > extend_object_data+6 and column_name in
['Objectnr VIIA', 'Objectpart', 'PGA', 'Importance factor', 'PGA design',
'Score cluster', 'Score construction year', 'Score material loadbearing walls',
'Score most floor height', 'Score thickness loadbearing walls', 'Score nr levels',
'Score other floor material', 'Score PGA', 'Score', 'Cluster', 'Construction year',
'Consequence class', 'Nr levels', 'Building height', 'Most floor height',
'Most thickness loadbearing walls', 'Material loadbearing walls', 'Ground floor material',
'Other floors material', 'Roof material', 'Box Link', 'Dat file link']) or \
(row_pointer <= extend_object_data+6 and column_name in table.column_names and hasattr(data_entry, k)):
value = getattr(data_entry, k)
if column_name == 'Box Link':
if value:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].hyperlink = value
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].value = 'Box link'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].style = 'Hyperlink'
else:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].value = '-'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].alignment = Alignment(horizontal='center')
continue
if column_name == 'Dat file link':
if value:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].hyperlink = value
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].value = 'Dat-file link'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].style = 'Hyperlink'
else:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].value = '-'
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)].alignment = Alignment(horizontal='center')
continue
if isinstance(value, list):
value = ', '.join(value)
if k == 'score' and value:
value = value / 100
elif k == 'most_thickness_load_walls' and value:
value = value * 1E3
elif k == 'total_cost' and value is None:
value = '-'
elif isinstance(value, PIL_Image):
value = openpyxl_Image(value) # convert PIL image to openpyxl image
# Resize cell dimensions
ws.row_dimensions[row_pointer].height = 100
col_letter = get_column_letter(table.column_names.index(column_mapping[k]) + 1)
ws.column_dimensions[col_letter].width = 20
# Anchor image to cell to enable image hiding with cells
_from = AnchorMarker(
col=table.column_names.index(column_mapping[k]),
row=row_pointer - 1)
to = AnchorMarker(
col=table.column_names.index(column_mapping[k]) + 1,
row=row_pointer)
value.anchor = TwoCellAnchor(editAs='twoCell', _from=_from, to=to)
value = ws.add_image(value)
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) + str(row_pointer)] = value
else:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) + str(row_pointer)] = '-'
# Save the target Excel file
wb.save(target_file)
project.write_log(f"Successfully created preselection excel-sheet for reference approach at '{target_file}'.")
return target_file
### ===================================================================================================================
### 3. End of script
### ===================================================================================================================