def create_fm_data_manager(restore=None):
"""
Creates a fm data manager using the xl instance passed.
WARNING! dont change the wrapper order.
:param restore:
:param xl:
:return:
"""
xl = xl_app()
assets = to_df(get_value("project_state_assets", xl=xl))
if (assets.shape[0] == 0) and restore is None:
message_box("Provide at least a section to start the analysis")
return
cfg = get_cached_object(get_value("data_obj_cfg"))
source_connector = create_source_connector(xl=xl)
query_manager = create_query_manager(xl=xl)
data_loader = FMDataLoader()
data_formatter = FMDataFormatter()
dm = FMDataManager(
cfg=cfg,
qm=query_manager,
sc=source_connector,
dl=data_loader,
df=data_formatter,
restore=restore,
)
return dm
def fm_data_manager_xl(
xl,
config_xl,
query_manager_xl,
source_connector_xl,
data_loader_xl,
data_formatter_xl,
request,
) -> FMDataManager:
dm = FMDataManager(
cfg=config_xl,
qm=query_manager_xl,
sc=source_connector_xl,
dl=data_loader_xl,
df=data_formatter_xl,
restore=request.param,
)
if request.param is None:
assets = to_df(get_value("project_state_assets", xl=xl))
type_curves = to_df(get_value("type_curves", xl=xl))
section_assumptions = to_df(get_value("section_assumptions", xl=xl))
external_settings = read_project_settings(string="sett_", xl=xl)
dm.set_new_session(
external_settings=external_settings,
assets=assets,
type_curves=type_curves,
section_assumptions=section_assumptions,
)
return dm
def set_dca_pars():
xl = xl_app()
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
selected_well = get_value("fm_fn_producing_well_name", xl=xl)
dca_pars_oil = pd.DataFrame(
dm.session.query(
Well_Oneline.ip_final_oil,
Well_Oneline.di_oil,
Well_Oneline.b_oil,
Well_Oneline.dmin_oil,
Well_Oneline.ip_oil_idx,
).filter(Well_Oneline.well_str == selected_well).all())
dca_pars_oil.columns = ["IP", "De", "B", "Dmin", "Max IP Month"]
dca_pars_gas = pd.DataFrame(
dm.session.query(
Well_Oneline.ip_final_gas,
Well_Oneline.di_gas,
Well_Oneline.b_gas,
Well_Oneline.dmin_gas,
Well_Oneline.ip_gas_idx,
).filter(Well_Oneline.well_str == selected_well).all())
dca_pars_gas.columns = ["IP", "De", "B", "Dmin", "Max IP Month"]
dca_pars = pd.concat([dca_pars_oil, dca_pars_gas])
copy_df_xl(
df=dca_pars,
sheet="Producing",
name="fm_fn_producing_dca_pars",
copy_columns=True,
xl=xl,
)
def set_well_list(xl=None):
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
selected_section = get_value("fm_fn_producing_section", xl=xl)
data = (dm.session.query(
Well_Oneline.well_str,
Well_Oneline.api,
Well_Oneline.operator_name,
Well_Oneline.well_type,
Well_Oneline.date_completion,
Well_Oneline.oil_gross_volume,
Well_Oneline.gas_gross_volume,
).join(Section_Well).filter(Section_Well.trsm_heh == selected_section))
df = pd.DataFrame(data).assign(
date_completion=lambda x: pd.to_datetime(x.date_completion))
df.columns = [
"Well Name",
"Api",
"Operator",
"Well Type",
"Completion Date",
"Cum Oil",
"Cum Gas",
]
# clear_list(containing_string="fm_fn_well_list", xl=xl)
copy_df_xl(df, "Producing", "well_list", copy_columns=True, xl=xl)
def debug_set_new_session(data_manager: FMDataManager,
xl: w32 = None) -> FMDataManager:
assets = to_df(get_value("project_state_assets", xl=xl))
type_curves = to_df(get_value("type_curves", xl=xl))
section_assumptions = to_df(get_value("section_assumptions", xl=xl))
data_manager.set_new_session(assets=assets,
type_curves=type_curves,
section_assumptions=section_assumptions)
return data_manager
def load_project_xml(xl=None):
project_specific = get_value("data_load_tabs", xl=xl)
logging.info(f"Loading {project_specific} ribbon")
cfg = get_cached_object(get_value("data_obj_cfg"))
ET.register_namespace("", cfg["PROJECT"]["xml_ns"])
ribbon_start = ET.fromstring(get_ribbon_xml())
project_xml = ET.parse(cfg["XML"][project_specific]).getroot()
ribbon_end = add_element(ribbon_start, project_xml)
ribbon_end_string = ET.tostring(ribbon_end,
encoding="unicode",
method="xml")
set_ribbon_xml(ribbon_end_string, reload=True)
def set_formation_normalizer(xl: w32 = None):
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
data = (dm.session.query(
Well_Oneline.api,
Well_Oneline.well_str,
Well_Oneline.operator_name,
Well_Oneline.formation,
Section_Assumption.formation_1,
Section_Assumption.formation_2,
Well_Oneline.norm_formation,
).join(Section_Well).join(Project_State_Asset).join(
Section_Assumption).all())
df = pd.DataFrame(data)
copy_df_xl(
df=df,
sheet="Formation",
name="formation_state_normalizer",
copy_columns=False,
xl=xl,
)
dm.data_formatter.formation_state_normalizer()
def save_as_session(trigger):
xl = xl_app()
cfg = get_cached_object(get_value("data_obj_cfg",
xl=xl)) # type: ParametersParser
save_file = file_saver("Save Session",
path=cfg["PROJECT"]["named"] + "/*" +
cfg["PROJECT"]["format"])
if save_file is None:
return
save_file = save_file + cfg["PROJECT"]["format"]
data_manager = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
data_manager.save_as_db(save_file)
logging.info(f"Saving Session as {save_file}")
def normalize_formation(trigger):
logging.info("Normalizing Formation")
xl = xl_app()
dm = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
data = (dm.session.query(
Well_Oneline.api,
Well_Oneline.well_name,
Well_Oneline.well_number,
Well_Oneline.well_str,
Well_Oneline.operator_name,
Well_Oneline.formation,
Section_Assumption.formation_1,
Section_Assumption.formation_2,
).join(Section_Well).join(Project_State_Asset).join(
Section_Assumption).all())
df = pd.DataFrame(data)
# This line added in case a multi unit well lying in two different sections have two different
# section assumptions
df = df.drop_duplicates(["api"]).reset_index(drop=True)
str_columns = [
"well_name",
"well_number",
"operator_name",
"formation",
"formation_1",
"formation_2",
]
string_array = df.loc[:, str_columns].values.transpose().astype("unicode")
formation = form_norm(string_array)
df["norm_formation"] = formation
formation_columns = [
"api",
"well_str",
"operator_name",
"formation",
"formation_1",
"formation_2",
"norm_formation",
]
df = df.loc[:, formation_columns]
with dm.session_scope() as session:
for i, row in df.iterrows():
selected_well = (session.query(Well_Oneline).filter(
Well_Oneline.api == row["api"]).one()) # type: Well_Oneline
selected_well.norm_formation = row.norm_formation
set_formation_normalizer()
def read_project_settings(string: str = "sett_", xl=None) -> pd.DataFrame:
settings_names = get_names_with_string(pattern=string, xl=xl)
settings_values = [
str(get_value(setting, xl=xl)) for setting in settings_names
]
settings = dict(name=settings_names, value=settings_values)
settings_df = pd.DataFrame(settings)
return settings_df
def set_producing_sections(xl=None):
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
sections = np.unique(
np.array(
dm.session.query(Section_Well.trsm_heh).join(Well_Oneline).filter(
Well_Oneline.well_type == "PDP").all()))
copy_np_xl(sections, "Helper", "fm_fn_producing_sections", xl=xl)
set_value("fm_fn_producing_section", sections[0])
def set_producing_section_wells():
xl = xl_app()
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
selected_section = get_value("fm_fn_producing_section", xl=xl)
wells = np.unique(
np.array(
dm.session.query(Well_Oneline.well_str).join(Section_Well).filter(
and_(
Well_Oneline.well_type == "PDP",
Section_Well.trsm_heh == selected_section,
)).all()))
clear_list(containing_string="fm_fn_producing_section_wells", xl=xl)
copy_np_xl(array=wells,
sheet="Helper",
name="fm_fn_producing_section_wells",
xl=xl)
def load_module_xml(module, xl=None):
cfg = get_cached_object(get_value("data_obj_cfg"))
ET.register_namespace("", cfg["PROJECT"]["xml_ns"])
ribbon_start = ET.fromstring(get_ribbon_xml())
project_xml = ET.parse(cfg["XML"][module]).getroot()
ribbon_end = add_element(ribbon_start, project_xml)
ribbon_end_string = ET.tostring(ribbon_end,
encoding="unicode",
method="xml")
set_ribbon_xml(ribbon_end_string, reload=True)
def reset_defaults(xl=None):
xl.ScreenUpdating = False
try:
logging.info("Resetting Defaults")
cfg = get_cached_object(
get_value("data_obj_cfg")) # type: ParametersParser
set_default_values(cfg["DEFAULTS_VALUES"])
set_default_formulae(cfg["DEFAULTS_FORMULAE"], xl=xl)
finally:
xl.ScreenUpdating = True
def load_pyxll_ribbon():
try:
xl = xl_app()
is_pyxll = get_value("data_pyxll_flag", xl=xl)
if is_pyxll:
logging.info("Setting pyxll ribbon")
cfg = ParametersParser()
cfg.read(get_value("data_cfg_path", xl=xl))
ET.register_namespace("", cfg["PROJECT"]["xml_ns"])
ribbon_start = ET.fromstring(get_ribbon_xml())
pyxll = ET.parse(cfg["XML"]["pyxll"]).getroot()
ribbon_end = add_element(ribbon_start, pyxll)
ribbon_end_string = ET.tostring(ribbon_end,
encoding="unicode",
method="xml")
set_ribbon_xml(ribbon_end_string, reload=True)
return True
except Exception:
return False
def get_data(trigger):
logging.info("Getting Data")
xl = xl_app()
set_formula("data_obj_manager", fn=create_fm_data_manager, xl=xl)
data_manager = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
assets = to_df(get_value("project_state_assets", xl=xl))
type_curves = to_df(get_value("type_curves", xl=xl))
section_assumptions = to_df(get_value("section_assumptions", xl=xl))
external_settings = read_project_settings(string="sett_", xl=xl)
data_manager.set_new_session(
external_settings=external_settings,
assets=assets,
type_curves=type_curves,
section_assumptions=section_assumptions,
)
data_manager.data_formatter.project_state_assets(xl=xl)
data_manager.data_formatter.project_parameters(xl=xl)
def load_session(trigger):
xl = xl_app()
cfg = get_cached_object(get_value("data_obj_cfg",
xl=xl)) # type: ParametersParser
backup_file = file_picker("Load Session",
path=cfg["PROJECT"]["BACKUP"] + "/*" +
cfg["PROJECT"]["format"])
if backup_file is None:
return
set_formula("data_obj_manager",
fn=create_fm_data_manager,
args=f'"{backup_file}"',
xl=xl)
data_manager = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
data_manager.check_compatibility()
load_user_zones(xl=xl)
set_formation_normalizer(xl=xl)
set_producing_sections(xl=xl)
def create_query_manager(xl=None):
"""
@type xl: win32com.client.Dispatch("Excel.Application")
:param xl:
:return:
"""
sql_queries = to_df(get_value("data_queries", xl=xl))
queries = list()
for query in sql_queries.itertuples():
new_connection = Query(query.name, query.db, query.query, query.filter,
query.function_call)
queries.append(new_connection)
query_manager = QueryManager(queries)
return query_manager
def create_source_connector(xl=None):
"""
@type xl: win32com.client.Dispatch("Excel.Application")
:param xl:
:return:
"""
connection_settings = to_df(get_value("data_servers", xl=xl))
sql_cnxns = list()
for cnxn in connection_settings.itertuples():
new_connection = SourceConnection(
cnxn.db,
cnxn.server,
cnxn.driver,
int(cnxn.port),
os.environ[cnxn.user],
os.environ[cnxn.pwd],
cnxn.auth,
)
sql_cnxns.append(new_connection)
sql_manager = SourceConnector(sql_cnxns)
return sql_manager
def decline_wells(trigger):
"""
TODO: Optimize this function. Take a longer time for lots of sections.
:param trigger:
:return:
"""
logging.info("Declining Wells")
xl = xl_app()
dm = get_cached_object(get_value("data_obj_manager",
xl=xl)) # type: FMDataManager
df = dm["monthlies"]
api_list = df.api.unique()
oil_list, gas_list = run_arps(df, api_list, dm.get_initial_guess(),
dm.get_bounds())
with dm.session_scope() as session:
for i, selected_api in enumerate(api_list):
selected_well = (session.query(Well_Oneline).filter(
Well_Oneline.api == str(selected_api)).one()
) # type: Well_Oneline
selected_well.ip_final_oil = oil_list[i][0]
selected_well.di_oil = oil_list[i][1]
selected_well.dmin_oil = oil_list[i][2]
selected_well.b_oil = oil_list[i][3]
selected_well.ip_oil_idx = oil_list[i][4]
selected_well.ip_final_gas = gas_list[i][0]
selected_well.di_gas = gas_list[i][1]
selected_well.dmin_gas = gas_list[i][2]
selected_well.b_gas = gas_list[i][3]
selected_well.ip_gas_idx = oil_list[i][4]
# selected_well.well_type = "Declined"
logging.info(f"Declined {api_list.shape[0]} wells")
set_producing_sections(xl=xl)
def section_assumptions_xl(xl) -> pd.DataFrame:
return to_df(get_value("section_assumptions", xl=xl))
def type_curves_xl(xl) -> pd.DataFrame:
return to_df(get_value("type_curves", xl=xl))
def assets_xl(xl) -> pd.DataFrame:
return to_df(get_value("project_state_assets", xl=xl))
def map_columns_range(self, name: str, xl: w32 = None):
address = get_address(name, xl=xl)
excel_columns = make_columns_from_range(address)
df_columns = to_df(get_value(name, xl=xl)).columns.values.tolist()
return {key: value for key, value in zip(df_columns, excel_columns)}
def config_xl(xl) -> ParametersParser:
cfg_path = get_value("data_cfg_path")
return load_config_path(cfg_path)