def GetActuals(WellorArea, Wedge, start_date, end_date, LEName = '', Adjusted = False, Phase = 'Gas'): from Model import ModelLayer as m import pandas as pd from Controller import SummaryModule as s from Model import ImportUtility as i from Model import QueryFile as qf from Model import BPXDatabase as bpx Success = True Messages = [] ActualProduction = [] try: WellList = [] if WellorArea: WellList = s.GetFullWellList(WellorArea) if Wedge: wedge_list, Success, Message = GetWellorAreaByWedge(Wedge) if not Success: Messages.append('Error finding wells associated with input Wedge. ') else: WellList.extend(wedge_list) EDWobj = bpx.GetDBEnvironment('ProdEDW', 'OVERRIDE') corpid_query = qf.EDWKeyQueryFromWellName(WellList) corpid_list = EDWobj.Query(corpid_query) corpid_list = list(corpid_list[1]['CorpID']) if LEName and Adjusted == True: actuals_df, Success, Message = i.ImportActuals(corpid_list, pd.to_datetime(start_date), pd.to_datetime(end_date), LEName) else: actuals_df, Success, Message = i.ImportActuals(corpid_list, pd.to_datetime(start_date), pd.to_datetime(end_date), '') if not Success: Messages.append(Message) else: dates = actuals_df['Date_Key'].unique() prod_array = [] date_array = [] if not actuals_df.empty: if Phase == 'Gas': for date in dates: results = actuals_df.query('Date_Key == @date') prod_array.append(results['Gas'].sum()) date_array.append(date) ActualProduction = ProductionData(date_array, prod_array, Phase,'scf') elif Phase == 'Water': for date in dates: results = actuals_df.query('Date_Key == @date') prod_array.append(results['Water'].sum()) date_array.append(date) ActualProduction = ProductionData(date_array, prod_array, Phase,'bbl') elif Phase == 'Oil': for date in dates: results = actuals_df.query('Date_Key == @date') prod_array.append(results['Oil'].sum()) date_array.append(date) ActualProduction = ProductionData(date_array, prod_array, Phase,'bbl') except Exception as ex: Success = False Messages.append('Error during import of Actual production data. ' + str(ex)) return ActualProduction, Success, Messages
def CalculateSummaryInfo(LEName, ForecastName, SummaryName, SummaryDate, Update_User): from Model import ModelLayer as m from Model import BPXDatabase as bpx from Model import ImportUtility as i import numpy as np import pandas as pd from datetime import datetime from datetime import timedelta import calendar from Controller import DataInterface as di Success = True Messages = [] try: #Get netting factor for each well #Calculate the Monthly AveragMBOED print('Gathering wedge information...') LEHeaderObj = m.LEHeader('', [], [], [LEName], []) Rows, Success, Message = LEHeaderObj.ReadTable() LEDataRowObj = m.LEData('', [LEName], [], []) DataRows, Success, Message = LEDataRowObj.ReadTable() if not Success or len(DataRows) == 0: if not Message: Message = 'No LE found in the database.' Messages.append(Message) else: well_list = [] for row in Rows: well_list.append(row.WellName) NettingObj = m.GasNetting('', well_list, []) NettingRows, Success, Message = NettingObj.ReadTable() if isinstance(SummaryDate, str): today = pd.to_datetime(SummaryDate) elif isinstance(SummaryDate, datetime): today = SummaryDate else: today = datetime.today() first_of_month = datetime(today.year, today.month, 1) first_of_year = datetime(today.year, 1, 1) quarter_start = pd.to_datetime(today - pd.tseries.offsets.QuarterBegin( startingMonth=1)).date() quarter_end = pd.to_datetime(today + pd.tseries.offsets.QuarterEnd( startingMonth=3)).date() next_quarter_start = quarter_end + timedelta(days=1) month_end = pd.to_datetime(today + pd.tseries.offsets.MonthEnd(1)).date() tomorrow = today + timedelta(days=1) year_end = datetime(today.year, 12, 31) Rows = pd.DataFrame([vars(s) for s in Rows]) DataRows = pd.DataFrame([vars(s) for s in DataRows]) #This will be the end of the LE forecast plus one day. next_day_date = max(DataRows['Date_Key']) + timedelta(days=1) le_start_date = min(DataRows['Date_Key']) le_end_date = max(DataRows['Date_Key']) #Create a dataframe that calculates total and also line item by wedge #loop through each wedge wedge_list = Rows['Wedge'].unique() wedge_totals = [] for wedge in wedge_list: #Eventually, may need to add oil and water in this as other phases are implemented wedge_total = {} wedge_rows = Rows.query('Wedge == @wedge') #Gather well list for this particular wedge and obtain: # 1.) Actuals up to current day # 2.) LE Forecast values to end of month # - if LE Forecast does not extend to end of month, send warning message and get GFO values to end of year # 3.) GFO Forecast values from first of next month to year end. # 4.) Average all of the values and multiply by Netting Value and Frac Hit Multipliers for that forecast. # - If no frac hit multipliers exist, default to 1. (send message) # - If no Netting Values exist, default to 1. (send message) print('Importing wedge actuals...') corpIDs = wedge_rows['CorpID'].unique() corpIDs = list(corpIDs) all_corpids = i.GetFullWellList(corpIDs) ytd_actuals_df, Success, Messages = i.ImportActuals( all_corpids, first_of_year, today, LEName) first_of_month_date_only = datetime.date(first_of_month) today_date_only = datetime.date(today) mtd_actuals_df = ytd_actuals_df.query( 'Date_Key >= @first_of_month_date_only and Date_Key < @today_date_only' ) qtd_actuals_df = ytd_actuals_df.query( 'Date_Key >= @quarter_start and Date_Key < @today_date_only' ) now = datetime.now() days_in_month = calendar.monthrange(now.year, now.month)[1] days_in_quarter = (quarter_end - quarter_start).days days_in_year = 365 print('Gathering wedge GFO data for \'' + wedge + '\' ...') gfo_annually_wedge_df, Success, Message = GetGFOValues( ForecastName, corpIDs, first_of_year, year_end) if not Success: Messages.append(Message) else: gfo_monthly_wedge_df = gfo_annually_wedge_df.query( 'Date_Key >= @first_of_month and Date_Key <= @month_end' ) gfo_quarterly_wedge_df = gfo_annually_wedge_df.query( 'Date_Key >= @quarter_start and Date_Key <= @quarter_end' ) if Success: #Generate Daily Netting Values based on table entries for each CorpID GasNettingObj = m.GasNetting('', [], all_corpids) GasNettingValues, Success, Message = GasNettingObj.ReadTable( ) if len(GasNettingValues) > 0: GasNettingValues = pd.DataFrame( [vars(s) for s in GasNettingValues]) Gas_Daily_Nf = GenerateDailyNF(GasNettingValues, first_of_year, year_end) Gas_Daily_Nf.rename(columns={'NettingValue': 'GasNF'}, inplace=True) else: Gas_Daily_Nf = pd.DataFrame( columns=['GasNF', 'Date_Key', 'CorpID']) OilNettingObj = m.OilNetting('', [], all_corpids) OilNettingValues, Success, Message = OilNettingObj.ReadTable( ) if len(OilNettingValues) > 0: OilNettingValues = pd.DataFrame( [vars(s) for s in OilNettingValues]) Oil_Daily_Nf = GenerateDailyNF(OilNettingValues, first_of_year, year_end) Oil_Daily_Nf.rename(columns={'NettingValue': 'OilNF'}, inplace=True) else: Oil_Daily_Nf = pd.DataFrame( columns=['OilNF', 'Date_Key', 'CorpID']) #Merge the NF tables daily_Nf = pd.merge(Gas_Daily_Nf, Oil_Daily_Nf, left_on=['Date_Key', 'CorpID'], right_on=['Date_Key', 'CorpID'], how='outer') daily_Nf.fillna(0, inplace=True) summary = [] if not Success: Messages.append(Message) else: MultipliersObj = m.FracHitMultipliers( '', [LEName], [], []) MultiplierRows, Success, Message = MultipliersObj.ReadTable( ) if MultiplierRows: MultiplierRows = pd.DataFrame( [vars(s) for s in MultiplierRows]) print('Calculating wedge summary information for \'' + wedge + '\' ...') count = 1 for corpID in corpIDs: row = {} well_df = Rows.query('CorpID == @corpID') row['WellName'] = well_df['WellName'].iloc[0] row['CorpID'] = corpID row['Wedge'] = well_df['Wedge'].iloc[0] area_corpids = i.GetFullWellList([corpID]) #Monthly data #loop through actuals well_mtd_actual_df = GetActualsFromWellorArea( mtd_actuals_df, area_corpids) well_ytd_actual_df = GetActualsFromWellorArea( ytd_actuals_df, area_corpids) well_qtd_actual_df = GetActualsFromWellorArea( qtd_actuals_df, area_corpids) #Get Netting Factors and Multipliers from Frac Hit Mitigation well_multipliers_df = pd.DataFrame() annual_default_multipliers_df = GenerateDefaultMultipliers( 1, first_of_year, year_end) if not MultiplierRows.empty: well_multipliers_df = MultiplierRows.query( 'CorpID == @corpID') else: Messages.append( 'No multiplier values found, using default value of 1.' ) well_multipliers_df = annual_default_multipliers_df well_mtd_actual_sum = SumDailyValues( well_mtd_actual_df, daily_Nf, annual_default_multipliers_df, 'MeasuredGas', 'MeasuredOil') well_ytd_actual_sum = SumDailyValues( well_ytd_actual_df, daily_Nf, annual_default_multipliers_df, 'MeasuredGas', 'MeasuredOil') well_qtd_actual_sum = SumDailyValues( well_qtd_actual_df, daily_Nf, annual_default_multipliers_df, 'MeasuredGas', 'MeasuredOil') well_gfo_monthly_df = gfo_monthly_wedge_df.query( 'CorpID == @corpID') well_gfo_quarterly_df = gfo_quarterly_wedge_df.query( 'CorpID == @corpID') well_gfo_annually_df = gfo_annually_wedge_df.query( 'CorpID == @corpID') well_forecast_name = well_df[ 'ForecastGeneratedFrom'].iloc[0] gfo_after_le_to_yearend_df, Success, Message = GetGFOValues( well_forecast_name, corpIDs, next_day_date, year_end) gfo_after_le_to_month_df = gfo_after_le_to_yearend_df.query( 'Date_Key >= @next_day_date and Date_Key <= @month_end' ) gfo_after_le_to_quarter_df = gfo_after_le_to_yearend_df.query( 'Date_Key >= @next_day_date and Date_Key <= @quarter_end' ) well_gfo_after_le_month_df = gfo_after_le_to_month_df.query( 'CorpID == @corpID') well_gfo_after_le_quarter_df = gfo_after_le_to_quarter_df.query( 'CorpID == @corpID') well_gfo_after_le_annual_df = gfo_after_le_to_yearend_df.query( 'CorpID == @corpID') #Get GFO Sums well_gfo_monthly_sum_midmonth = GetMidMonthGFOValue( well_gfo_monthly_df, 'Gas_Production', 'Oil_Production') well_gfo_quarterly_sum_midmonth = GetMidMonthGFOValue( well_gfo_quarterly_df, 'Gas_Production', 'Oil_Production') well_gfo_annually_sum_midmonth = GetMidMonthGFOValue( well_gfo_annually_df, 'Gas_Production', 'Oil_Production') #Convert NF to method expected dataframe from forecast table forecast_NF_dict = {} # forecast_NF_dict['GasNF'] = well_gfo_after_le_annual_df['GasNF'].values # forecast_NF_dict['OilNF'] = well_gfo_after_le_annual_df['OilNF'].values forecast_NF_dict[ 'Date_Key'] = well_gfo_after_le_annual_df[ 'Date_Key'].values forecast_NF_dict['CorpID'] = [ corpID ] * well_gfo_after_le_annual_df.shape[0] forecast_NF_df = pd.DataFrame(forecast_NF_dict) well_gfo_after_le_month_sum = SumDailyValues( well_gfo_after_le_month_df, forecast_NF_df, annual_default_multipliers_df, 'Gas_Production', 'Oil_Production') well_gfo_after_le_quarter_sum = SumDailyValues( well_gfo_after_le_quarter_df, forecast_NF_df, annual_default_multipliers_df, 'Gas_Production', 'Oil_Production') well_gfo_after_le_annual_sum = SumDailyValues( well_gfo_after_le_annual_df, forecast_NF_df, annual_default_multipliers_df, 'Gas_Production', 'Oil_Production') well_le_df_all = DataRows.query( 'CorpID == @corpID') well_le_df_month = well_le_df_all.query( 'Date_Key >= @today_date_only and Date_Key <= @month_end' ) well_le_df_total = well_le_df_all.query( 'Date_Key > @month_end') well_le_sum_total = SumDailyValues( well_le_df_total, daily_Nf, well_multipliers_df, 'Gas_Production', 'Oil_Production') well_le_sum_month = SumDailyValues( well_le_df_month, daily_Nf, well_multipliers_df, 'Gas_Production', 'Oil_Production') row['LE_Monthly'] = (well_mtd_actual_sum + well_le_sum_month + well_gfo_after_le_month_sum) row['GFOzMonthly'] = well_gfo_monthly_sum_midmonth row['LE_Quarterly'] = ( well_qtd_actual_sum + well_le_sum_month + well_le_sum_total + well_gfo_after_le_quarter_sum) row['GFOzQuarterly'] = ( well_gfo_quarterly_sum_midmonth) row['LE_Annually'] = (well_ytd_actual_sum + well_le_sum_month + well_le_sum_total + well_gfo_after_le_annual_sum) row['GFOzAnnually'] = ( well_gfo_annually_sum_midmonth) summary.append(row) di.callprogressbar(count, len(corpIDs)) count = count + 1 print('Summarizing wedge data...') if summary: summary_df = pd.DataFrame(summary) length = summary_df.shape[0] wedge_total['wedge_name'] = wedge wedge_total['LE_Monthly'] = summary_df[ 'LE_Monthly'].sum() / days_in_month / 1000 wedge_total['GFOzMonthly'] = summary_df[ 'GFOzMonthly'].sum() / days_in_month / 1000 wedge_total['MonthlyVariance'] = wedge_total[ 'LE_Monthly'] - wedge_total['GFOzMonthly'] wedge_total['LE_Quarterly'] = summary_df[ 'LE_Quarterly'].sum() / days_in_quarter / 1000 wedge_total['GFOzQuarterly'] = summary_df[ 'GFOzQuarterly'].sum() / days_in_quarter / 1000 wedge_total['QuarterlyVariance'] = wedge_total[ 'LE_Quarterly'] - wedge_total['GFOzQuarterly'] wedge_total['LE_Annually'] = summary_df[ 'LE_Annually'].sum() / days_in_year / 1000 wedge_total['GFOzAnnually'] = summary_df[ 'GFOzAnnually'].sum() / days_in_year / 1000 wedge_total['AnnualVariance'] = wedge_total[ 'LE_Annually'] - wedge_total['GFOzAnnually'] wedge_totals.append(wedge_total) print('Writing to database...') for row in wedge_totals: #Create a Summary entry WedgeName = row['wedge_name'] Midstream = '' Reason = '' Comments = '' LEName = LEName GFOForecastName = ForecastName MonthlyAvgMBOED = row['LE_Monthly'] QuarterlyAvgMBOED = row['LE_Quarterly'] AnnualAvgMBOED = row['LE_Annually'] MonthlyGFOMBOED = row['GFOzMonthly'] QuarterlyGFOMBOED = row['GFOzQuarterly'] AnnualGFOMBOED = row['GFOzAnnually'] MonthlyVariance = row['MonthlyVariance'] QuarterlyVariance = row['QuarterlyVariance'] AnnualVariance = row['AnnualVariance'] SummaryObj = m.LESummaryRow( SummaryName, WedgeName, Midstream, Reason, Comments, SummaryDate, LEName, GFOForecastName, MonthlyAvgMBOED, QuarterlyAvgMBOED, AnnualAvgMBOED, MonthlyGFOMBOED, QuarterlyGFOMBOED, AnnualGFOMBOED, MonthlyVariance, QuarterlyVariance, AnnualVariance, '') Write_Success, Message = SummaryObj.Write( Update_User, datetime.now()) if not Write_Success: Messages.append(Message) except Exception as ex: Sucess = False Messages.append('Error during calculation of summary information. ' + str(ex)) return Success, Messages