friction_loss.outputs.create_template
1import io 2from pathlib import Path 3from typing import Union, Dict, Optional 4 5import numpy as np 6from custom_plugin_base.server.calculation import SolveFileContent 7from custom_plugin_base.services.clients.unisum_client import UnisumClient 8from custom_plugin_base.utils.service_enum import LangEnum 9from xlsxwriter import Workbook 10 11from friction_loss.utils.const import DEFAULT_VALUE_TEMPLATE, PARAMETERS_NAMES, MM_TO_INCHES 12from friction_loss.utils.converting_units import convert_template_data 13from friction_loss.utils.plugin_dataclasses import PolypresInputParameter 14from friction_loss.utils.pumps import get_all_pump_database 15from friction_loss.utils.set_language import (PARAMETERS_NAMES_TRANSLATOR, NAMES_COLUMNS_TRANSLATOR, 16 UNIT_NAMES_TRANSLATOR, COLUMNS_PUMP_DATABASE_TRANSLATOR, 17 UNIT_SYSTEMS, PARAMETERS_COMMENTS_TRANSLATOR) 18 19 20def create_excel_template(language: LangEnum, unit: str, well_names: str) -> SolveFileContent: 21 """ 22 Create input data template in Excel file format 23 24 Parameters 25 ---------- 26 language: LangEnum 27 Language of interface 28 unit: str 29 Units of project 30 well_names: str 31 Line with well names 32 """ 33 output = io.BytesIO() 34 35 default_val = convert_template_data(data=DEFAULT_VALUE_TEMPLATE, units=unit, language=language) 36 37 create_excel_table(language=language, unit=unit, path=output, data=default_val, well_names=well_names) 38 39 excel_bytes = output.getvalue() 40 41 template = SolveFileContent(name="Friction_loss_template.xlsx", 42 content_type='BytesIO', 43 content=excel_bytes) 44 return template 45 46 47def create_excel_table(language: LangEnum, unit: str, path: Union[Path, io.BytesIO], 48 data: Union[Dict[str, PolypresInputParameter], np.ndarray], 49 well_names: str, md_tvdss: Optional[np.ndarray] = None) -> None: 50 """ 51 Create input data template in Excel file format 52 53 Parameters 54 ---------- 55 language: LangEnum 56 Language of interface 57 unit: str 58 Units of project 59 path: os.Path | io.BytesIO 60 os.Path to file Excel 61 data: dict[str, PolypresInputParameter] | np.ndarray 62 keys : parameter names 63 values : dataclass with rows of table parameters 64 well_names: str 65 Line with well names 66 md_tvdss: np.ndarray(dict) 67 Array with MD and TVDss coordinates 68 """ 69 workbook = Workbook(path) 70 names = well_names.split(', ') 71 worksheets = np.empty(len(names), dtype=object) 72 style_center = {'align': 'center', 'border': 1} 73 74 params_sheet = workbook.add_worksheet(NAMES_COLUMNS_TRANSLATOR[language]['params sheet']) 75 # Create worksheets file Excel with well construction wells information 76 for i, name in enumerate(names): 77 worksheets[i] = workbook.add_worksheet(name) 78 79 # Create worksheet file Excel with pump database 80 worksheet_pump_data_base = workbook.add_worksheet(NAMES_COLUMNS_TRANSLATOR[language]['pump']) 81 _create_pump_data_base_sheet(workbook, worksheet_pump_data_base, language, unit) 82 last_row = worksheet_pump_data_base.dim_rowmax + 1 # +1 Так как не учитывается строка заголовков 83 name_sheet_in_quote = "'" + str(NAMES_COLUMNS_TRANSLATOR[language]["pump"]) + "'" 84 85 # Creating an Excel table page with plugin parameters 86 _create_plugin_params_sheet(workbook, params_sheet, language, unit) 87 88 # Create worksheets file Excel with well construction wells information 89 for i, worksheet in enumerate(worksheets): 90 worksheet.set_zoom(130) 91 if isinstance(data, dict): 92 input_data = data 93 else: 94 input_data = data[i] 95 _create_well_construction_sheet(workbook, worksheet, language, input_data) 96 worksheet.data_validation('B13', {'validate': 'list', 'source': f'={name_sheet_in_quote}!$A$2:$A${last_row}'}) 97 98 if md_tvdss is not None: 99 md_tvdss_i = md_tvdss[i] 100 if md_tvdss_i['md'] is None or md_tvdss_i['tvdss'] is None: 101 continue 102 # Добавили MD и TVDss координаты 103 for j in range(len(md_tvdss_i['md'])): 104 worksheet.write(f'D{i+2}', md_tvdss_i['md'][j], workbook.add_format(style_center)) 105 worksheet.write(f'E{i + 2}', md_tvdss_i['tvdss'][j], workbook.add_format(style_center)) 106 workbook.close() 107 108 109def _create_plugin_params_sheet(workbook, worksheet, language: LangEnum, unit: str) -> None: 110 worksheet.set_column(0, 0, 29) 111 worksheet.set_column(1, 1, 15) 112 113 style_left = {'align': 'left', 'border': 1} 114 style_center = {'align': 'center', 'border': 1} 115 116 # Имена столбцов 117 worksheet.write('A1', NAMES_COLUMNS_TRANSLATOR[language]['param'], 118 workbook.add_format({'bold': True, 'align': 'center', 'border': 1})) 119 worksheet.write('B1', NAMES_COLUMNS_TRANSLATOR[language]['val'], 120 workbook.add_format({'bold': True, 'align': 'center', 'border': 1})) 121 122 comments_array = [70, 70, 45, 45, 60] 123 # Добавление названий строк и рамок колонок 124 for i, name in enumerate(['start point', 'ref point', 'units len', 'units diam', 'report']): 125 worksheet.write(f'A{i+2}', NAMES_COLUMNS_TRANSLATOR[language][name], 126 workbook.add_format(style_left)) 127 worksheet.write_comment(f'A{i+2}', PARAMETERS_COMMENTS_TRANSLATOR[language][name], 128 {'width': 150, 'height': comments_array[i]}) 129 worksheet.write(f'B{i+2}', '', workbook.add_format(style_center)) 130 131 # Значение ячеек единиц измерения 132 worksheet.data_validation(f'B{4}', 133 {'validate': 'list', 'source': UNIT_NAMES_TRANSLATOR[language]['length']}) 134 worksheet.write(f'B{4}', UNIT_SYSTEMS[language][unit]['Length'], workbook.add_format(style_center)) 135 worksheet.data_validation(f'B{5}', 136 {'validate': 'list', 'source': UNIT_NAMES_TRANSLATOR[language]['diameter']}) 137 worksheet.write(f'B{5}', UNIT_SYSTEMS[language][unit]['Diameter'], workbook.add_format(style_center)) 138 139 # Добавление строки с флагом генерации отчета 140 worksheet.data_validation('B6', {'validate': 'list', 141 'source': UNIT_NAMES_TRANSLATOR[language]['report']}) 142 worksheet.write('B6', UNIT_NAMES_TRANSLATOR[language]['report'][-1], 143 workbook.add_format(style_center)) 144 145 worksheet.set_zoom(130) 146 147 148def _create_well_construction_sheet(workbook, worksheet, language: LangEnum, 149 default_value: Dict[str, PolypresInputParameter]) -> None: 150 """ 151 Creating an Excel table page with well construction parameters. 152 153 Parameters 154 ---------- 155 workbook 156 Excel table. 157 worksheet 158 Sheet of Excel table. 159 language: LangEnum 160 Language of interface. 161 default_value: dict[str, PolypresInputParameter] 162 Well construction data. 163 """ 164 style_left = {'align': 'left', 'border': 1} 165 166 # Имена столбцов 167 worksheet.write('A1', NAMES_COLUMNS_TRANSLATOR[language]['param'], 168 workbook.add_format({'bold': True, 'align': 'center', 'border': 1})) 169 worksheet.write('B1', NAMES_COLUMNS_TRANSLATOR[language]['val'], 170 workbook.add_format({'bold': True, 'align': 'center', 'border': 1})) 171 172 # Задание размера ячеек 173 worksheet.set_column(0, 0, 27) 174 worksheet.set_column(1, 1, 24) 175 176 for i, name in enumerate(PARAMETERS_NAMES): 177 worksheet.write(f'A{i + 2}', PARAMETERS_NAMES_TRANSLATOR[language][name], workbook.add_format(style_left)) 178 worksheet.write_comment(f'A{i + 2}', PARAMETERS_COMMENTS_TRANSLATOR[language][name]) 179 180 worksheet.write(f'B{i + 2}', default_value[name].value, 181 workbook.add_format(style_left)) # величина по умолчанию 182 183 # MD координаты скважины 184 worksheet.set_column(3, 3, 10) 185 worksheet.write('D1', 'MD', workbook.add_format({'bold': True, 'align': 'center'})) 186 worksheet.write_comment('D1', PARAMETERS_COMMENTS_TRANSLATOR[language]['md'], 187 {'width': 150, 'height': 45}) 188 189 # TVDss координаты скважины 190 worksheet.set_column(4, 4, 10) 191 worksheet.write('E1', 'TVDss', workbook.add_format({'bold': True, 'align': 'center'})) 192 worksheet.write_comment('E1', PARAMETERS_COMMENTS_TRANSLATOR[language]['tvdss'], 193 {'width': 150, 'height': 90}) 194 195 196def _create_pump_data_base_sheet(workbook, worksheet, language: LangEnum, units: str): 197 """ 198 Creating an Excel table page with pumps database. 199 200 Parameters 201 ---------- 202 workbook 203 Excel table. 204 worksheet 205 Sheet of Excel table. 206 language: LangEnum 207 Language of interface. 208 units: str 209 Units of project. 210 """ 211 # База данных всех насосов (кастомные насосы и насосы Полипреса) 212 pump_database = get_all_pump_database() 213 214 style_head = {'bold': True, 'align': 'center', 'border': 1} 215 216 pump_db = COLUMNS_PUMP_DATABASE_TRANSLATOR[language] 217 unisum_convert = UnisumClient() 218 idx = 2 # Индекс строки таблицы 219 220 # Строка заголовков таблицы 221 worksheet.write('A1', pump_db['name'], workbook.add_format(style_head)) 222 worksheet.write('B1', pump_db['q_nominal'] + UNIT_SYSTEMS[language][units]['Flow rate'], 223 workbook.add_format(style_head)) 224 worksheet.write('C1', pump_db['head_nominal'] + UNIT_SYSTEMS[language][units]['Pressure'], 225 workbook.add_format(style_head)) 226 worksheet.write('D1', pump_db['f_nominal'], workbook.add_format(style_head)) 227 worksheet.write('E1', pump_db['pumpsize'] + UNIT_SYSTEMS[language][units]['Diameter'], 228 workbook.add_format(style_head)) 229 worksheet.write('F1', pump_db['k_f'], workbook.add_format(style_head)) 230 worksheet.write('G1', pump_db['stages_count'], workbook.add_format(style_head)) 231 worksheet.write('H1', pump_db['pump_koeffs'], workbook.add_format(style_head)) 232 worksheet.write('I1', pump_db['hp_koeffs'], workbook.add_format(style_head)) 233 234 # Размеры ячеек заданы исходя из размера данных 235 worksheet.set_column(0, 0, 33) 236 worksheet.set_column(1, 1, 30) 237 worksheet.set_column(2, 2, 25) 238 worksheet.set_column(3, 3, 24) 239 worksheet.set_column(4, 4, 18) 240 worksheet.set_column(5, 5, 23) 241 worksheet.set_column(6, 6, 18) 242 worksheet.set_column(7, 7, 65) 243 worksheet.set_column(8, 8, 66) 244 245 # Параметры насосов 246 for name, value in pump_database.items(): 247 if value.pump_koeffs is None: 248 value.pump_koeffs = '-' 249 if value.hp_koeffs is None: 250 value.hp_koeffs = '-' 251 252 # перевод в нужные ЕИ 253 q_nom = unisum_convert.convert_one('m^3/day', UNIT_SYSTEMS[LangEnum.US][units]['Flow rate'], value.q_nominal) 254 dp_nom = unisum_convert.convert_one('Pa', UNIT_SYSTEMS[LangEnum.US][units]['Pressure'], value.dp_nominal) 255 if units == 'Field': 256 pump_size = value.pumpsize / MM_TO_INCHES # mm ->in 257 else: 258 pump_size = value.pumpsize 259 260 if idx % 2 == 0: 261 style_body = {'align': 'center', 'border': 1} 262 else: 263 style_body = {'align': 'center', 'border': 1, 'bg_color': '#D3D3D3'} 264 265 worksheet.write(f'A{idx}', name, workbook.add_format(style_body)) 266 worksheet.write(f'B{idx}', q_nom, workbook.add_format(style_body)) 267 worksheet.write(f'C{idx}', dp_nom, workbook.add_format(style_body)) 268 worksheet.write(f'D{idx}', value.f_nominal, workbook.add_format(style_body)) 269 worksheet.write(f'E{idx}', pump_size, workbook.add_format(style_body)) 270 worksheet.write(f'F{idx}', value.k_f, workbook.add_format(style_body)) 271 worksheet.write(f'G{idx}', value.stages_count, workbook.add_format(style_body)) 272 worksheet.write(f'H{idx}', str(value.pump_koeffs), workbook.add_format(style_body)) 273 worksheet.write(f'I{idx}', str(value.hp_koeffs), workbook.add_format(style_body)) 274 275 idx += 1 276 277 # Колонка 'нет насоса' 278 if idx % 2 == 0: 279 style_body = {'align': 'center', 'border': 1} 280 else: 281 style_body = {'align': 'center', 'border': 1, 'bg_color': '#D3D3D3'} 282 283 worksheet.write(f'A{idx}', NAMES_COLUMNS_TRANSLATOR[language]['no_pump'], workbook.add_format(style_body)) 284 for letter in ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']: 285 worksheet.write(f'{letter}{idx}', '-', workbook.add_format(style_body))
def
create_excel_template( language: custom_plugin_base.utils.service_enum.LangEnum, unit: str, well_names: str) -> custom_plugin_base.server.calculation.SolveFileContent:
21def create_excel_template(language: LangEnum, unit: str, well_names: str) -> SolveFileContent: 22 """ 23 Create input data template in Excel file format 24 25 Parameters 26 ---------- 27 language: LangEnum 28 Language of interface 29 unit: str 30 Units of project 31 well_names: str 32 Line with well names 33 """ 34 output = io.BytesIO() 35 36 default_val = convert_template_data(data=DEFAULT_VALUE_TEMPLATE, units=unit, language=language) 37 38 create_excel_table(language=language, unit=unit, path=output, data=default_val, well_names=well_names) 39 40 excel_bytes = output.getvalue() 41 42 template = SolveFileContent(name="Friction_loss_template.xlsx", 43 content_type='BytesIO', 44 content=excel_bytes) 45 return template
Create input data template in Excel file format
Parameters
language: LangEnum Language of interface unit: str Units of project well_names: str Line with well names
def
create_excel_table( language: custom_plugin_base.utils.service_enum.LangEnum, unit: str, path: Union[pathlib.Path, _io.BytesIO], data: Union[Dict[str, friction_loss.utils.plugin_dataclasses.PolypresInputParameter], numpy.ndarray], well_names: str, md_tvdss: Union[numpy.ndarray, NoneType] = None) -> None:
48def create_excel_table(language: LangEnum, unit: str, path: Union[Path, io.BytesIO], 49 data: Union[Dict[str, PolypresInputParameter], np.ndarray], 50 well_names: str, md_tvdss: Optional[np.ndarray] = None) -> None: 51 """ 52 Create input data template in Excel file format 53 54 Parameters 55 ---------- 56 language: LangEnum 57 Language of interface 58 unit: str 59 Units of project 60 path: os.Path | io.BytesIO 61 os.Path to file Excel 62 data: dict[str, PolypresInputParameter] | np.ndarray 63 keys : parameter names 64 values : dataclass with rows of table parameters 65 well_names: str 66 Line with well names 67 md_tvdss: np.ndarray(dict) 68 Array with MD and TVDss coordinates 69 """ 70 workbook = Workbook(path) 71 names = well_names.split(', ') 72 worksheets = np.empty(len(names), dtype=object) 73 style_center = {'align': 'center', 'border': 1} 74 75 params_sheet = workbook.add_worksheet(NAMES_COLUMNS_TRANSLATOR[language]['params sheet']) 76 # Create worksheets file Excel with well construction wells information 77 for i, name in enumerate(names): 78 worksheets[i] = workbook.add_worksheet(name) 79 80 # Create worksheet file Excel with pump database 81 worksheet_pump_data_base = workbook.add_worksheet(NAMES_COLUMNS_TRANSLATOR[language]['pump']) 82 _create_pump_data_base_sheet(workbook, worksheet_pump_data_base, language, unit) 83 last_row = worksheet_pump_data_base.dim_rowmax + 1 # +1 Так как не учитывается строка заголовков 84 name_sheet_in_quote = "'" + str(NAMES_COLUMNS_TRANSLATOR[language]["pump"]) + "'" 85 86 # Creating an Excel table page with plugin parameters 87 _create_plugin_params_sheet(workbook, params_sheet, language, unit) 88 89 # Create worksheets file Excel with well construction wells information 90 for i, worksheet in enumerate(worksheets): 91 worksheet.set_zoom(130) 92 if isinstance(data, dict): 93 input_data = data 94 else: 95 input_data = data[i] 96 _create_well_construction_sheet(workbook, worksheet, language, input_data) 97 worksheet.data_validation('B13', {'validate': 'list', 'source': f'={name_sheet_in_quote}!$A$2:$A${last_row}'}) 98 99 if md_tvdss is not None: 100 md_tvdss_i = md_tvdss[i] 101 if md_tvdss_i['md'] is None or md_tvdss_i['tvdss'] is None: 102 continue 103 # Добавили MD и TVDss координаты 104 for j in range(len(md_tvdss_i['md'])): 105 worksheet.write(f'D{i+2}', md_tvdss_i['md'][j], workbook.add_format(style_center)) 106 worksheet.write(f'E{i + 2}', md_tvdss_i['tvdss'][j], workbook.add_format(style_center)) 107 workbook.close()
Create input data template in Excel file format
Parameters
language: LangEnum Language of interface unit: str Units of project path: os.Path | io.BytesIO os.Path to file Excel data: dict[str, PolypresInputParameter] | np.ndarray keys : parameter names values : dataclass with rows of table parameters well_names: str Line with well names md_tvdss: np.ndarray(dict) Array with MD and TVDss coordinates