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)