class WellDepth(WellBase):
__tablename__ = "depths"
name = db.Column(db.String(50), index=True, primary_key=True)
value = db.Column(db.Integer())
property_name = db.Column(db.String(50), index=True)
aggregate_type = db.Column(db.String(25), index=True)
grid_id = db.Column(db.Integer(), index=True)
formation = db.Column(db.String(50), index=True)
into_formation_feet = db.Column(db.Integer())
into_formation_percent = db.Column(db.Float())
above_next_formation_feet = db.Column(db.Integer())
above_next_formation_percent = db.Column(db.Float())
overlap_feet = db.Column(db.Integer())
overlap_percent = db.Column(db.Float())
in_target = db.Column(db.Boolean())
assignment_method = db.Column(db.String()) # TODO: enum
class SurveyPoint(WellBase):
__tablename__ = "survey_points"
md = db.Column(db.Integer(), primary_key=True)
tvd = db.Column(db.Integer())
dip = db.Column(db.Float())
sequence = db.Column(db.Integer())
theta = db.Column(db.Float())
is_in_lateral = db.Column(db.Boolean(), nullable=False, default=False)
is_heel_point = db.Column(db.Boolean(), nullable=False, default=False)
is_mid_point = db.Column(db.Boolean(), nullable=False, default=False)
is_toe_point = db.Column(db.Boolean(), nullable=False, default=False)
is_soft_corner = db.Column(db.Boolean(), nullable=False, default=False)
is_hard_corner = db.Column(db.Boolean(), nullable=False, default=False)
is_kop = db.Column(db.Boolean(), nullable=False, default=False)
geom = db.Column(db.Geometry("POINT", srid=4326, spatial_index=False))
ix_lateral_partial = db.Index(
"ix_lateral_partial",
"api14",
"is_in_lateral",
postgresql_where=(is_in_lateral),
)
ix_heel_partial = db.Index(
"ix_heel_partial",
"api14",
"is_heel_point",
postgresql_where=(is_heel_point),
)
ix_mid_partial = db.Index(
"ix_mid_partial",
"api14",
"is_mid_point",
postgresql_where=(is_mid_point),
)
ix_toe_partial = db.Index(
"ix_toe_partial",
"api14",
"is_toe_point",
postgresql_where=(is_toe_point),
)
ix_survey_point_geom = db.Index("ix_survey_point_geom",
"geom",
postgresql_using="gist")
class RuntimeStat(Base):
__tablename__ = "runtime_stats"
id = db.Column(db.BigInteger(), primary_key=True)
hole_direction = db.Column(db.String(25))
data_type = db.Column(db.String(25))
operation = db.Column(db.String(50))
name = db.Column(db.String(50))
time = db.Column(db.Numeric(19, 2))
count = db.Column(db.Integer())
class Area(Base):
__tablename__ = "areas"
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
area = db.Column(db.String(25), unique=True, nullable=False)
h_last_run_at = db.Column(db.DateTime(timezone=True))
v_last_run_at = db.Column(db.DateTime(timezone=True))
providers = db.Column(db.JSONB(), nullable=False, server_default="[]")
# entity_type = db.Column(
# db.ChoiceType(EntityType, impl=db.String()), primary_key=True
# )
# hole_direction = db.Column(
# db.ChoiceType(HoleDirection, impl=db.String()), primary_key=True
# )
@classmethod
def _is_ready(cls, last_run_at: Optional[datetime], cooldown_hours: int):
if last_run_at:
utcnow = datetime.now().astimezone(pytz.utc)
threshold = utcnow - timedelta(hours=cooldown_hours)
return last_run_at < threshold
else:
return True
@classmethod
async def next_available(
cls, hole_dir: HoleDirection) -> Tuple[Area, str, bool, int]:
""" Get the properties describing the next available execution time of
the given hole direction and entity type """
if hole_dir == HoleDirection.H:
cooldown = conf.PRODSTATS_H_COOLDOWN
elif hole_dir == HoleDirection.V:
cooldown = conf.PRODSTATS_V_COOLDOWN
else:
cooldown = ONE_WEEK
attr = f"{hole_dir.value.lower()}_last_run_at"
area_obj = await cls.query.order_by( # get stalest area for given hole_dir
getattr(cls, attr).asc().nullsfirst()).gino.first()
is_ready = cls._is_ready(getattr(area_obj, attr), cooldown)
logger.info(
f"({cls.__name__}[{hole_dir}]) next available: {area_obj.area} {is_ready=}" # noqa
)
return area_obj, attr, is_ready, cooldown
@classmethod
async def df(cls) -> pd.DataFrame:
records = await cls.query.gino.all()
return pd.DataFrame([x.to_dict() for x in records],
columns=cls.c.names).set_index("area")
class ProdStat(Base):
__tablename__ = "prodstats"
api10 = db.Column(db.String(10), primary_key=True)
name = db.Column(db.String(50), primary_key=True, index=True)
value = db.Column(db.Numeric(19, 2))
property_name = db.Column(db.String(50), index=True)
aggregate_type = db.Column(db.String(25), index=True)
is_peak_norm = db.Column(db.Boolean())
is_ll_norm = db.Column(db.Boolean())
ll_norm_value = db.Column(db.Integer())
includes_zeroes = db.Column(db.Boolean())
start_date = db.Column(db.Date())
end_date = db.Column(db.Date())
start_month = db.Column(db.Integer())
end_month = db.Column(db.Integer())
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
ix_prodstat_api10_prop_agg = db.Index("ix_prodstat_api10_prop_agg",
"api10", "property_name",
"aggregate_type")
class Survey(WellBase):
__tablename__ = "surveys"
survey_type = db.Column(db.String(50))
survey_method = db.Column(db.String(50))
survey_date = db.Column(db.Date())
survey_top = db.Column(db.Integer())
survey_top_uom = db.Column(db.String(10))
survey_base = db.Column(db.Integer())
survey_base_uom = db.Column(db.String(10))
# NOTE: alembic doesn't play nicely with spatial created by geoalchemy2. It
# will autogenerate them, then delete them in the next migration. The
# workaround here is to disable the automatic index creation by
# geoalchemy2 (spatial_index=False) and explictly add the gist indices
# using sqlalchemy.
wellbore = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
lateral_only = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
stick = db.Column(db.Geometry("LINESTRING", srid=4326,
spatial_index=False))
bent_stick = db.Column(
db.Geometry("LINESTRING", srid=4326, spatial_index=False))
ix_survey_wellbore = db.Index("ix_survey_wellbore",
"wellbore",
postgresql_using="gist")
ix_survey_lateral_only = db.Index("ix_survey_lateral_only",
"lateral_only",
postgresql_using="gist")
ix_survey_stick = db.Index("ix_survey_stick",
"stick",
postgresql_using="gist")
ix_survey_bent_stick = db.Index("ix_survey_bent_stick",
"bent_stick",
postgresql_using="gist")
class FracParameters(WellBase):
__tablename__ = "frac_parameters"
fluid_bbl = db.Column(db.Integer())
proppant_lb = db.Column(db.Integer())
fluid_bbl_ft = db.Column(db.Integer())
proppant_lb_ft = db.Column(db.Integer())
lateral_length = db.Column(db.Integer())
lateral_length_type = db.Column(db.String(25))
gen = db.Column(db.Integer())
gen_name = db.Column(db.String(10))
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
class WellHeader(WellBase):
__tablename__ = "wells"
api10 = db.Column(db.String(10), index=True)
well_name = db.Column(db.String())
hole_direction = db.Column(db.String(1))
status = db.Column(db.String(50))
is_producing = db.Column(db.Boolean(), index=True)
operator = db.Column(db.String(), index=True)
operator_alias = db.Column(db.String(), index=True)
hist_operator = db.Column(db.String(), index=True)
hist_operator_alias = db.Column(db.String(), index=True)
tvd = db.Column(db.Integer())
md = db.Column(db.Integer())
perfll = db.Column(db.Integer())
lateral_length = db.Column(db.Integer())
ground_elev = db.Column(db.Integer())
kb_elev = db.Column(db.Integer())
comp_date = db.Column(db.Date())
spud_date = db.Column(db.Date())
permit_date = db.Column(db.Date())
permit_number = db.Column(db.String())
permit_status = db.Column(db.String())
rig_release_date = db.Column(db.Date()) # rr_date
last_activity_date = db.Column(db.Date())
basin = db.Column(db.String(50), index=True) # basin
sub_basin = db.Column(db.String(50), index=True) # basin
state = db.Column(db.String(50)) # state_name
state_code = db.Column(db.String(10)) # state_name
county = db.Column(db.String(50), index=True) # county_name
county_code = db.Column(db.String(10)) # county_name
provider_status = db.Column(db.String(50))
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
basin_holedir_isprod_idx = db.Index("ix_well_basin_holedir_isprod",
"basin", "hole_direction",
"is_producing")
basin_status_idx = db.Index("ix_well_basin_status", "basin", "status")
class TestModel(Base):
__tablename__ = "test_model"
id = db.Column(db.Integer(), index=True, primary_key=True)
name = db.Column(db.String())
class IPTest(WellBase):
__tablename__ = "ip_tests"
test_number = db.Column(db.Integer(), primary_key=True)
test_date = db.Column(db.Date(), index=True)
type_code = db.Column(db.String(10))
test_method = db.Column(db.String())
completion = db.Column(db.Integer())
oil = db.Column(db.Integer())
oil_per10k = db.Column(db.Integer())
oil_uom = db.Column(db.String(10))
gas = db.Column(db.Integer())
gas_per10k = db.Column(db.Integer())
gas_uom = db.Column(db.String(10))
water = db.Column(db.Integer())
water_per10k = db.Column(db.Integer())
water_uom = db.Column(db.String(10))
choke = db.Column(db.String(25))
depth_top = db.Column(db.Integer())
depth_top_uom = db.Column(db.String(10))
depth_base = db.Column(db.Integer())
depth_base_uom = db.Column(db.String(10))
sulfur = db.Column(db.Boolean())
oil_gravity = db.Column(db.Float())
oil_gravity_uom = db.Column(db.String(10))
gor = db.Column(db.Integer())
gor_uom = db.Column(db.String(10))
perf_upper = db.Column(db.Integer())
perf_upper_uom = db.Column(db.String(10))
perf_lower = db.Column(db.Integer())
perf_lower_uom = db.Column(db.String(10))
perfll = db.Column(db.Integer())
perfll_uom = db.Column(db.String(10))
class ProdHeader(Base):
__tablename__ = "production_header"
api10 = db.Column(db.String(10), primary_key=True)
primary_api14 = db.Column(db.String(14), index=True)
entity12 = db.Column(db.String(12), nullable=False)
status = db.Column(db.String())
first_prod_date = db.Column(db.Date())
last_prod_date = db.Column(db.Date())
prod_months = db.Column(db.Integer())
prod_days = db.Column(db.Integer())
peak_norm_months = db.Column(db.Integer())
peak_norm_days = db.Column(db.Integer())
peak30_oil = db.Column(db.Integer())
peak30_gas = db.Column(db.Integer())
peak30_date = db.Column(db.Date())
peak30_month = db.Column(db.Integer())
perfll = db.Column(db.Integer())
perf_upper = db.Column(db.Integer())
perf_lower = db.Column(db.Integer())
oil_pdp_last3mo_per30kbbl = db.Column(db.Integer())
boe_pdp_last3mo_per30kbbl = db.Column(db.Integer())
products = db.Column(db.String())
provider = db.Column(db.String())
provider_last_update_at = db.Column(db.DateTime(timezone=True))
related_well_count = db.Column(db.Integer())
related_wells = db.Column(db.JSONB(), nullable=False, server_default="[]")
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")
class ProdMonthly(Base):
__tablename__ = "production_monthly"
api10 = db.Column(db.String(10), primary_key=True)
prod_date = db.Column(db.Date(), primary_key=True)
prod_month = db.Column(db.Integer())
days_in_month = db.Column(db.Integer())
prod_days = db.Column(db.Integer())
peak_norm_month = db.Column(db.Integer())
peak_norm_days = db.Column(db.Integer()) # currently prod_day
oil = db.Column(db.Integer())
gas = db.Column(db.Integer())
water = db.Column(db.Integer())
boe = db.Column(db.Integer())
water_cut = db.Column(db.Numeric(19, 2))
oil_percent = db.Column(db.Numeric(19, 2))
gor = db.Column(db.Integer())
oil_per1k = db.Column(db.Integer())
gas_per1k = db.Column(db.Integer())
water_per1k = db.Column(db.Integer())
boe_per1k = db.Column(db.Integer())
gas_per3k = db.Column(db.Integer())
oil_per3k = db.Column(db.Integer())
water_per3k = db.Column(db.Integer())
boe_per3k = db.Column(db.Integer())
gas_per5k = db.Column(db.Integer())
oil_per5k = db.Column(db.Integer())
water_per5k = db.Column(db.Integer())
boe_per5k = db.Column(db.Integer())
gas_per7500 = db.Column(db.Integer())
oil_per7500 = db.Column(db.Integer())
water_per7500 = db.Column(db.Integer())
boe_per7500 = db.Column(db.Integer())
gas_per10k = db.Column(db.Integer())
oil_per10k = db.Column(db.Integer())
water_per10k = db.Column(db.Integer())
boe_per10k = db.Column(db.Integer())
oil_avg_daily = db.Column(db.Integer())
gas_avg_daily = db.Column(db.Numeric(19, 2))
water_avg_daily = db.Column(db.Numeric(19, 2))
boe_avg_daily = db.Column(db.Numeric(19, 2))
comments = db.Column(db.JSONB(), nullable=False, server_default="{}")