class ToxicsWorstCase(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='toxic_id',
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE
)
percent_weight = CopyFromDecimalField(
max_digits=4,
decimal_places=1,
null=True,
)
# percent_weight = CopyFromCharField(max_length=7)
physical_state = CopyFromCharField(max_length=1, blank=True)
analytical_basis = CopyFromCharField(max_length=255, blank=True)
scenario = CopyFromCharField(max_length=1, blank=True)
# quantity_lbs = CopyFromDecimalField(max_digits=6, decimal_places=2, blank=True)
quantity_lbs = CopyFromCharField(max_length=4, blank=True)
# release_duration = CopyFromDecimalField(max_digits=7, decimal_places=2, blank=True)
release_duration = CopyFromCharField(max_length=7, blank=True)
# release_rate = CopyFromDecimalField(max_digits=4, decimal_places=1, blank=True)
release_rate = CopyFromCharField(max_length=7, blank=True)
wind_speed = CopyFromDecimalField(max_digits=4, decimal_places=1, blank=True)
stability_class = CopyFromCharField(max_length=1, blank=True)
topography = CopyFromCharField(max_length=1, blank=True)
# endpoint_distance = CopyFromDecimalField(max_digits=5, decimal_places=1)
endpoint_distance = CopyFromCharField(max_length=4, blank=True)
population = CopyFromCharField(max_length=9, blank=True)
pr_schools = CopyFromBooleanField()
pr_residences = CopyFromBooleanField()
pr_hospitals = CopyFromBooleanField()
pr_prisons = CopyFromBooleanField()
pr_public_rec= CopyFromBooleanField()
pr_comm_ind = CopyFromBooleanField()
pr_othertype = CopyFromCharField(max_length=200, blank=True)
er_natlstateparks = CopyFromBooleanField()
er_wildlifesanct = CopyFromBooleanField()
er_fedwilderness = CopyFromBooleanField()
er_othertype = CopyFromCharField(max_length=200, blank=True)
pm_dikes = CopyFromBooleanField()
pm_enclosures = CopyFromBooleanField()
pm_berms = CopyFromBooleanField()
pm_drains = CopyFromBooleanField()
pm_sumps = CopyFromBooleanField()
pm_othertype = CopyFromCharField(max_length=200, blank=True)
ptrgraphic = CopyFromCharField(max_length=12, blank=True)
cbi_flag = CopyFromBooleanField()
source_file = 'rmp_worst_tox'
class Tbls6AccidentChemicals(BaseRMPModel):
accidentchemicalid = CopyFromIntegerField(
source_column='AccidentChemicalID',
primary_key=True,
verbose_name='Accident Chemical Record ID',
help_text='A unique ID for each accident chemical record.',
)
# accidenthistoryid = CopyFromForeignKey(
# 'Tbls6Accidenthistory',
# on_delete=models.PROTECT,
accidenthistoryid = CopyFromIntegerField(
source_column='AccidentHistoryID',
help_text='The unique ID for each accident record',
)
# TODO: ForeignKeyField candidate
chemicalid = CopyFromForeignKey(
'ChemCd',
on_delete=models.PROTECT,
source_column='ChemicalID',
help_text='The identifying ID for a particular chemical released in an '
'accident.',
)
quantityreleased = CopyFromDecimalField(
source_column='QuantityReleased',
decimal_places=1,
max_digits=8,
null=True,
verbose_name='Amount Released (lbs)',
help_text='The amount of the substance released in the accident, in '
'pounds, to two significant digits.',
)
percentweight = CopyFromDecimalField(
source_column='PercentWeight',
decimal_places=2,
null=True,
max_digits=5,
verbose_name='Percent Weight (Within Mixture)',
help_text='The percent weight of a chemical within a mixture released '
'in an accident.',
)
source_file = 'tblS6AccidentChemicals'
class AccChem(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='accchem_id',
verbose_name='Accident Chemical Record ID',
help_text='A unique ID for each accident chemical record.',
)
accident = CopyFromForeignKey(
'Accident',
on_delete=models.PROTECT,
help_text='The unique ID for each accident record',
)
chemical = CopyFromForeignKey(
'ChemCd',
on_delete=models.PROTECT,
help_text='The identifying ID for a particular chemical released in an '
'accident.',
)
quantity_lbs = CopyFromIntegerField(
null=True,
verbose_name='Amount Released (lbs)',
help_text='The amount of the substance released in the accident, in '
'pounds, to two significant digits.',
)
percent_weight = CopyFromDecimalField(
decimal_places=2,
null=True,
max_digits=5,
verbose_name='Percent Weight (Within Mixture)',
help_text='The percent weight of a chemical within a mixture released '
'in an accident.',
)
num_acc_flam = CopyFromIntegerField(
null=True,
verbose_name='Number of Flammable Components',
help_text='The number of listed flammable component chemicals for this'
' chemical record.',
)
cas = CopyFromCharField(
max_length=9,
verbose_name='CAS number',
help_text='The identifying CAS number for a chemical.',
)
CHEMICAL_TYPE_CHOICES = (
('T', 'toxic'),
('F', 'flammable'),
)
chemical_type = CopyFromCharField(
max_length=1,
choices=CHEMICAL_TYPE_CHOICES,
help_text='"The type of chemical.',
)
source_file = 'rmp_acc_chem'
class ChemCd(BaseRMPModel):
id = CopyFromIntegerField(
source_column='ChemicalID',
primary_key=True,
help_text="RMP's unique identifier of a chemical substance.",
)
chemical_name = CopyFromCharField(
max_length=92,
help_text="Chemical substance name.",
)
cas2 = CopyFromCharField(
source_column='CASNumber',
max_length=10,
blank=True,
help_text="Chemical Abstracts Service (CAS) registry number "
"(is 2 meaningful?)",
)
threshold = CopyFromDecimalField(
source_column='threshold',
null=True,
decimal_places=1,
max_digits=8,
help_text='Threshold above which the chemical is regulated???')
chemical_type = CopyFromCharField(
source_column='ChemType',
max_length=1,
choices=CHEMICAL_TYPES,
blank=True,
help_text='"The type of chemical (T=toxic, F=Flammable).',
)
cbi_flag = CopyFromBooleanField(
source_column='flgCBI',
help_text='Indicates whether this record contained Confidential '
'Business Information (CBI) which has been erased by EPA '
'from the public version of the data.')
chemical_owner = CopyFromCharField(
source_column='ChemOwner',
max_length=3,
blank=True,
)
@classmethod
def get_transform_queryset(self):
m = raw_models.tlkpChemicals
return m.objects.get_default_transform_queryset()
class tlkpChemicals(BaseRMPModel):
chemicalid = CopyFromIntegerField(
source_column='ChemicalID',
primary_key=True,
help_text="Unique Identifier for a Chemical.",
)
chemicalname = CopyFromCharField(
source_column='ChemicalName',
max_length=92,
help_text="The name of the regulated chemical above the threshold "
"quantity in a Process at the source.",
)
casnumber = CopyFromCharField(
source_column='CASNumber',
max_length=10,
blank=True,
help_text="Chemical Abstract Service (CAS) registry number for the "
"chemical.")
threshold = CopyFromDecimalField(
source_column='Threshold',
null=True,
decimal_places=1,
max_digits=8,
help_text="Is the chemical quantity limit upon which reporting is "
"required.")
chemtype = CopyFromCharField(
source_column='ChemType',
max_length=1,
choices=CHEMICAL_TYPE_CHOICES,
blank=True,
help_text='"Chemical Type - (T)oxic or (F)lammable.',
)
flgcbi = CopyFromBooleanField(
source_column='flgCBI',
help_text="An indication that the Chemical is determined to be "
"Confidential Business Information (CBI). ‘Y’es or ‘N’o.")
chemowner = CopyFromCharField(source_column='ChemOwner',
max_length=3,
blank=True,
help_text="FED or State Abreviation")
source_file = 'tlkpChemicals'
class Meta:
verbose_name = 'Lookup: Process Chemical'
class ChemCd(BaseRMPModel):
id = CopyFromIntegerField(
source_column='chemical_id',
primary_key=True,
help_text="RMP's unique identifier of a chemical substance.",
)
chemical_name = CopyFromCharField(
source_column='chemical_name',
max_length=92,
help_text="Chemical substance name.",
)
cas2 = CopyFromCharField(
source_column='cas2',
max_length=10,
blank=True,
help_text="Chemical Abstracts Service (CAS) registry number "
"(is 2 meaningful?)",
)
threshold = CopyFromDecimalField(
source_column='threshold',
null=True,
decimal_places=1,
max_digits=8,
help_text='Threshold above which the chemical is regulated???'
)
chemical_type = CopyFromCharField(
source_column='chemical_type',
max_length=1,
choices=CHEMICAL_TYPE_CHOICES,
blank=True,
help_text='"The type of chemical (T=toxic, F=Flammable).',
)
cbi_flag = CopyFromBooleanField(
source_column='cbi_flag',
help_text='Indicates whether this record contained Confidential '
'Business Information (CBI) which has been erased by EPA '
'from the public version of the data.'
)
chemical_owner = CopyFromCharField(
source_column='chemical_owner',
max_length=3,
blank=True,
)
source_file = 'rmp_chem_cd'
class tblS1ProcessChemicals(BaseRMPModel):
processchemicalid = CopyFromIntegerField(
primary_key=True,
source_column='ProcessChemicalID',
verbose_name='Process Chemical ID',
help_text='Unique number used to identify each chemical within a '
'single RMP. Generated by RMP*Submit and 3rd-party programs.')
processid = CopyFromForeignKey(
'tblS1Processes',
on_delete=models.PROTECT,
source_column='ProcessID',
verbose_name='Process ID',
help_text='Unique number used to identify each covered process within '
'an RMP from Section 1 Program Level for the Covered '
'Process. Generated by RMP*Submit and 3rd-party programs.',
)
chemicalid = CopyFromForeignKey(
'tlkpChemicals',
on_delete=models.PROTECT,
source_column='ChemicalID',
verbose_name='Chemical ID',
help_text='Chemical Abstract Service (CAS) registry number for the '
'chemical.',
)
quantity = CopyFromDecimalField(
max_digits=28,
decimal_places=16,
null=True,
source_column='Quantity',
verbose_name='1.17.c.3 Quantity',
help_text='The maximum inventory quantity of the regulated substance '
'or mixture in the process in pounds.',
)
cbi_flag = CopyFromBooleanField(
source_column='CBI_Flag',
verbose_name='CBI Flag',
help_text='An indication that the quantity was claimed as CBI.',
)
class Meta:
verbose_name = 'Process: Chemical'
verbose_name_plural = 'Process: Chemicals'
class Facility(BaseRMPModel):
id = CopyFromBigIntegerField(
primary_key=True,
source_column='facility_id',
)
facility_name = CopyFromCharField(
max_length=50,
)
# ForeignKey Candidate?
rmp = CopyFromForeignKey(
'Registration',
on_delete=models.PROTECT,
)
street_1 = CopyFromCharField(
# check that values going into this field match facility_str_1
max_length=35,
)
street_2 = CopyFromCharField(
# check that values going into this field match facility_str_2
max_length=35,
)
city = CopyFromCharField(
max_length=19,
)
state = CopyFromCharField(max_length=2)
zip_code = CopyFromCharField(
max_length=5,
source_column='zip',
)
zip_ext = CopyFromCharField(max_length=4)
county_fips = CopyFromIntegerField()
num_registrations = CopyFromIntegerField()
latitude = CopyFromDecimalField(
source_column='latitude_dec',
max_digits=6,
decimal_places=3,
)
longitude = CopyFromDecimalField(
source_column='longitude_dec',
max_digits=6,
decimal_places=3,
)
num_registration = CopyFromIntegerField()
sub_type = CopyFromCharField(max_length=1, blank=True)
sub_date = CopyFromDateTimeField()
exec_type = CopyFromCharField(max_length=1, blank=True)
execsum_rmp = CopyFromForeignKey(
'ExecutiveSummary',
on_delete=models.PROTECT,
)
exec_sub_type = CopyFromCharField(max_length=1, blank=True)
exec_sub_date = CopyFromDateTimeField()
# these fields could be converted to DateTime once we replace "0000-00-00" with NULL
deregistration_date = CopyFromCharField(max_length=10)
dereg_effect_date = CopyFromCharField(max_length=10)
parent = CopyFromCharField(max_length=200, blank=True)
parent_2 = CopyFromCharField(max_length=200, blank=True)
operator_name = CopyFromCharField(max_length=200, blank=True)
operator_city = CopyFromCharField(max_length=20, blank=True)
operator_state = CopyFromCharField(max_length=2, blank=True)
operator_zip = CopyFromCharField(max_length=5, blank=True)
province = CopyFromCharField(max_length=20, blank=True)
county = CopyFromCharField(max_length=200, blank=True)
country = CopyFromCharField(max_length=25, blank=True)
sub_reason = CopyFromCharField(max_length=3, blank=True)
dereg_reason = CopyFromCharField(max_length=1, blank=True)
dereg_other = CopyFromCharField(max_length=255, blank=True)
# TODO AGGREGATE
toxic_tot = CopyFromIntegerField()
flam_tot = CopyFromBigIntegerField()
quantity_tot = CopyFromBigIntegerField() # toxic_tot + flam_tot
num_proc_23 = CopyFromBigIntegerField()
toxic_tot_23 = CopyFromIntegerField()
flam_tot_23 = CopyFromBigIntegerField()
quantity_tot_23 = CopyFromBigIntegerField() # toxic_tot + flam_tot
all_naics = CopyFromCharField(max_length=20, blank=True)
sortid_1 = CopyFromCharField(max_length=5)
sortid_2 = CopyFromCharField(max_length=5)
sortid_3 = CopyFromCharField(max_length=5)
deregistration_yn = CopyFromCharField(max_length=1, blank=True)
num_fte = CopyFromIntegerField(null=True)
# TODO AGGREGATE
num_accident = CopyFromIntegerField()
acc_flam_tot = CopyFromIntegerField()
acc_toxic_tot = CopyFromIntegerField()
acc_quantity_tot = CopyFromIntegerField()
num_deaths = CopyFromIntegerField()
num_injuries = CopyFromIntegerField()
num_evacuated = CopyFromIntegerField()
property_damage = CopyFromIntegerField()
class ToxicsAltRelease(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='toxic_id',
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE,
source_column='ProcessChemicalID',
)
percent_weight = CopyFromDecimalField(
max_digits=4,
decimal_places=1,
null=True,
)
physical_state = CopyFromForeignKey(
"PhysCd",
on_delete=models.PROTECT,
db_column='physical_state',
null=True,
)
analytical_basis = CopyFromCharField(
max_length=255,
blank=True,
)
scenario = CopyFromForeignKey(
"ScenCd",
on_delete=models.PROTECT,
db_column='scenario',
null=True,
)
quantity_released = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
null=True,
)
release_duration = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
null=True,
)
release_rate = CopyFromBooleanField(null=True, )
wind_speed = CopyFromDecimalField(
max_digits=6,
decimal_places=2,
null=True,
)
stability_class = CopyFromCharField(
max_length=1,
blank=True,
)
topography = CopyFromForeignKey(
"TopoCd",
on_delete=models.PROTECT,
db_column='topography',
null=True,
)
endpoint_distance = CopyFromDecimalField(
source_column='distance2_endpoint',
max_digits=5,
decimal_places=1,
null=True,
)
residential_population = CopyFromIntegerField(
null=True,
verbose_name='Residential population',
)
pr_schools = CopyFromBooleanField(verbose_name='Schools')
pr_residences = CopyFromBooleanField(verbose_name='Residences')
pr_hospitals = CopyFromBooleanField(verbose_name='Hospitals')
pr_prisons = CopyFromBooleanField(
verbose_name='Prisons/Correctional Facilities')
pr_public_recreation = CopyFromBooleanField(
verbose_name='Recreation Areas')
pr_comm_ind = CopyFromBooleanField(
verbose_name='Major Commercial, office, industrial areas')
pr_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
er_natl_state_parks = CopyFromBooleanField(
verbose_name='National or state parks, forests, or monuments', )
er_wildlife_sactuary = CopyFromBooleanField(
verbose_name=
'Officially designated wildlife sanctuaries, preserves, or refuges', )
er_fed_wilderness = CopyFromBooleanField(
verbose_name='Federal wilderness area', )
er_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
pm_dikes = CopyFromBooleanField(verbose_name='Dikes', )
pm_enclosures = CopyFromBooleanField(verbose_name='Enclosures', )
pm_berms = CopyFromBooleanField(verbose_name='Berms', )
pm_drains = CopyFromBooleanField(verbose_name='Drains', )
pm_sumps = CopyFromBooleanField(verbose_name='Sumps', )
pm_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
am_sprinkler_systems = CopyFromBooleanField(
verbose_name='Sprinkler systems')
am_deluge_systems = CopyFromBooleanField(verbose_name='Deluge systems')
am_water_curtain = CopyFromBooleanField(verbose_name='Water curtain')
am_neutralization = CopyFromBooleanField(verbose_name='Neutralization')
am_excess_flow_valve = CopyFromBooleanField(
verbose_name='Excess flow valve')
am_flares = CopyFromBooleanField(verbose_name='Flares')
am_scrubbers = CopyFromBooleanField(verbose_name='Scrubbers')
am_emergency_shutdown = CopyFromBooleanField(
verbose_name='Emergency shutdown')
am_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
ptr_graphic = CopyFromCharField(
max_length=12,
blank=True,
)
cbi_flag = CopyFromBooleanField()
@classmethod
def get_transform_queryset(self):
m = raw_models.tblS3ToxicsAltReleases
return m.objects.get_default_transform_queryset()
@property
def public_receptors_within_distance(self):
self._public_receptors_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pr_')
if self.__dict__[f.name]
]
if self.pr_other_type != '':
self._public_receptors_within_distance.append(self.pr_other_type)
return self._public_receptors_within_distance
@property
def public_receptors_not_within_distance(self):
self._public_receptors_not_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pr_')
if not self.__dict__[f.name]
]
return self._public_receptors_not_within_distance
@property
def environmental_receptors_within_distance(self):
self._environmental_receptors_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('er_')
if self.__dict__[f.name]
]
if self.er_other_type != '':
self._environmental_receptors_within_distance.append(
self.er_other_type)
return self._environmental_receptors_within_distance
@property
def environmental_receptors_not_within_distance(self):
self._environmental_receptors_not_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('er_')
if not self.__dict__[f.name]
]
return self._environmental_receptors_not_within_distance
@property
def passive_mitigation_considered(self):
self._passive_mitigation_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pm_')
if self.__dict__[f.name]
]
if self.pm_other_type != '':
self._passive_mitigation_considered.append(self.pm_other_type)
return self._passive_mitigation_considered
@property
def passive_mitigation_not_considered(self):
self._passive_mitigation_not_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pm_')
if not self.__dict__[f.name]
]
return self._passive_mitigation_not_considered
@property
def active_mitigation_considered(self):
self._active_mitigation_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('am_')
if self.__dict__[f.name]
]
if self.am_other_type != '':
self._active_mitigation_considered.append(self.pm_other_type)
return self._active_mitigation_considered
@property
def active_mitigation_not_considered(self):
self._active_mitigation_not_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('am_')
if not self.__dict__[f.name]
]
return self._active_mitigation_not_considered
class FlammablesWorstCase(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='flammable_id',
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE,
source_column='ProcessChemicalID',
)
analytical_basis = CopyFromCharField(
max_length=255,
blank=True,
)
distance2_endpoint = CopyFromDecimalField(
max_digits=5,
decimal_places=1,
null=True,
)
quantity_released = CopyFromIntegerField(null=True, )
residential_population = CopyFromIntegerField(null=True, )
pr_schools = CopyFromBooleanField(verbose_name='Schools')
pr_residences = CopyFromBooleanField(verbose_name='Residences')
pr_hospitals = CopyFromBooleanField(verbose_name='Hospitals')
pr_prisons = CopyFromBooleanField(
verbose_name='Prisons/Correctional Facilities')
pr_public_recreation = CopyFromBooleanField(
verbose_name='Recreation Areas')
pr_comm_ind = CopyFromBooleanField(
verbose_name='Major Commercial, office, industrial areas')
pr_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
er_natl_state_parks = CopyFromBooleanField(
verbose_name='National or state parks, forests, or monuments', )
er_wildlife_sactuary = CopyFromBooleanField(
verbose_name=
'Officially designated wildlife sanctuaries, preserves, or refuges', )
er_fed_wilderness = CopyFromBooleanField(
verbose_name='Federal wilderness area', )
er_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
pm_blast_walls = CopyFromBooleanField(verbose_name='Blast walls')
pm_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
ptr_graphic = CopyFromCharField(
max_length=12,
blank=True,
)
cbi_flag = CopyFromBooleanField()
@classmethod
def get_transform_queryset(self):
m = raw_models.tblS4FlammablesWorstCase
return m.objects.get_default_transform_queryset()
@property
def public_receptors_within_distance(self):
self._public_receptors_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pr_')
if self.__dict__[f.name]
]
if self.pr_other_type != '':
self._public_receptors_within_distance.append(self.pr_other_type)
return self._public_receptors_within_distance
@property
def public_receptors_not_within_distance(self):
self._public_receptors_not_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pr_')
if not self.__dict__[f.name]
]
return self._public_receptors_not_within_distance
@property
def environmental_receptors_within_distance(self):
self._environmental_receptors_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('er_')
if self.__dict__[f.name]
]
if self.er_other_type != '':
self._environmental_receptors_within_distance.append(
self.er_other_type)
return self._environmental_receptors_within_distance
@property
def environmental_receptors_not_within_distance(self):
self._environmental_receptors_not_within_distance = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('er_')
if not self.__dict__[f.name]
]
return self._environmental_receptors_not_within_distance
@property
def passive_mitigation_considered(self):
self._passive_mitigation_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pm_')
if self.__dict__[f.name]
]
if self.pm_other_type != '':
self._passive_mitigation_considered.append(self.pm_other_type)
return self._passive_mitigation_considered
@property
def passive_mitigation_not_considered(self):
self._passive_mitigation_not_considered = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('pm_')
if not self.__dict__[f.name]
]
return self._passive_mitigation_not_considered
class Accident(BaseRMPModel):
"""
Possible additions from Registration: Facility_name, city, county, parent_1? This would turn Process, Accident and Registration into the top level tables.
"""
id = CopyFromIntegerField(
primary_key=True,
source_column='accident_id',
)
rmp = CopyFromForeignKey(
'Registration',
on_delete=models.PROTECT,
)
accident_date = CopyFromDateField(null=True, )
accident_time = CopyFromCharField(
max_length=4,
blank=True,
)
naics_code = CopyFromForeignKey(
'NAICS',
db_column='naics_code',
on_delete=models.PROTECT,
)
release_duration = CopyFromCharField(max_length=5)
re_gas = CopyFromBooleanField(verbose_name='Gas release', )
re_spill = CopyFromBooleanField(verbose_name='Liquid spills/evaporation', )
re_fire = CopyFromBooleanField(verbose_name='Fire', )
re_explosion = CopyFromBooleanField(verbose_name='Explosion', )
re_reactive_incident = CopyFromBooleanField(
verbose_name='Uncontrolled/runaway reaction', )
rs_storage_vessel = CopyFromBooleanField(verbose_name='Storage vessel', )
rs_piping = CopyFromBooleanField(verbose_name='Piping', )
rs_process_vessel = CopyFromBooleanField(verbose_name='Process vessel', )
rs_transfer_hose = CopyFromBooleanField(verbose_name='Transfer hose', )
rs_valve = CopyFromBooleanField(verbose_name='Valve', )
rs_pump = CopyFromBooleanField(verbose_name='Pump', )
rs_joint = CopyFromBooleanField(verbose_name='Joint', )
other_release_source = CopyFromCharField(max_length=200, blank=True)
wind_speed = CopyFromFloatField(null=True, )
wind_speed_unit = CopyFromForeignKey(
'WindCd',
null=True,
on_delete=models.PROTECT,
db_column='wind_speed_unit',
)
wind_direction = CopyFromCharField(max_length=3, blank=True)
temperature = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
null=True,
)
stability_class = CopyFromCharField(max_length=1, blank=True)
precipitation = CopyFromBooleanField()
unknown_weather = CopyFromBooleanField()
deaths_workers = CopyFromIntegerField(null=True)
deaths_responders = CopyFromIntegerField(null=True)
deaths_public = CopyFromIntegerField(null=True)
injuries_workers = CopyFromIntegerField(null=True)
injuries_responders = CopyFromIntegerField(null=True)
injuries_public = CopyFromIntegerField(null=True)
onsite_damage = CopyFromIntegerField(null=True)
offsite_deaths = CopyFromIntegerField(null=True)
hospitalization = CopyFromIntegerField(null=True)
offsite_medical = CopyFromIntegerField(null=True)
offsite_evacuated = CopyFromIntegerField(null=True)
offsite_shelter = CopyFromIntegerField(null=True)
offsite_damage = CopyFromIntegerField(null=True)
ed_kills = CopyFromBooleanField(verbose_name='Fish or animal kills', )
ed_defoliation = CopyFromBooleanField(
verbose_name='Tree, lawn, shrub, or crop damage', )
ed_water_contamination = CopyFromBooleanField(
verbose_name='Water contamination', )
ed_soil_contamination = CopyFromBooleanField(
verbose_name='Soil Contamination', )
ed_other = CopyFromCharField(max_length=200, blank=True)
initiating_event = CopyFromForeignKey(
'EventsCd',
null=True,
blank=True,
on_delete=models.PROTECT,
db_column='initiating_event',
)
cf_equipment_failure = CopyFromBooleanField(
verbose_name='Equipment failure', )
cf_human_error = CopyFromBooleanField(verbose_name='Human error', )
cf_improper_procedure = CopyFromBooleanField(
verbose_name='Improper procedure', )
cf_overpressure = CopyFromBooleanField(verbose_name='Overpressurization', )
cf_upset_condition = CopyFromBooleanField(verbose_name='Upset condition', )
cf_bypass_condition = CopyFromBooleanField(
verbose_name='By-pass condition', )
cf_maintenance = CopyFromBooleanField(
verbose_name='Maintenance activity/inactivity', )
cf_process_design_failure = CopyFromBooleanField(
verbose_name='Process design failure', )
cf_unsuitable_equipment = CopyFromBooleanField(
verbose_name='Unsuitable equipment', )
cf_unusual_weather = CopyFromBooleanField(
verbose_name='Unusual weather conditions', )
cf_management_error = CopyFromBooleanField(
verbose_name='Management error', )
cf_other = CopyFromCharField(
max_length=200,
blank=True,
)
offsite_responders_notify = CopyFromCharField(
max_length=25,
blank=True,
)
ci_improved_equipment = CopyFromBooleanField(
verbose_name='Improved/upgraded equipment', )
ci_revised_maintenance = CopyFromBooleanField(
verbose_name='Revised maintenance', )
ci_revised_training = CopyFromBooleanField(
verbose_name='Revised training', )
ci_revised_op_procedures = CopyFromBooleanField(
verbose_name='Revised operating procedures', )
ci_new_process_controls = CopyFromBooleanField(
verbose_name='New process controls', )
ci_new_mitigation_systems = CopyFromBooleanField(
verbose_name='New mitigation systems', )
ci_response_plan = CopyFromBooleanField(
verbose_name='Revised emergency response plan', )
ci_changed_process = CopyFromBooleanField(verbose_name='Changed process', )
ci_reduced_inventory = CopyFromBooleanField(
verbose_name='Reduced inventory', )
ci_none = CopyFromBooleanField(verbose_name='None', )
ci_other = CopyFromCharField(max_length=200, blank=True)
cbi_flag = CopyFromBooleanField()
num_acc_chem = CopyFromIntegerField(null=True)
flam_total = CopyFromIntegerField(null=True)
toxic_total = CopyFromIntegerField(null=True)
quantity_total = CopyFromIntegerField(null=True)
num_deaths = CopyFromIntegerField(null=True)
num_injuries = CopyFromIntegerField(null=True)
num_evacuated = CopyFromIntegerField(null=True)
property_damage = CopyFromIntegerField(null=True)
@classmethod
def get_transform_queryset(self):
"""
This is the top level containing information about accidents at different facilities.
Aggregated fields:
num_acc_chem = Counts AccidentIDs in tblS6AccidentChemicals
flam_total, tox_total = Sums QuantityReleased column in accident chemicals depending on whether the ChemicalType flag is 'T' or 'F'
quantity_total = Sum of flam_total and tox_total for each accident
num_deaths = Sum of worker, public responder, and citizen deaths
num_injuries = Sum of worker, public responder, and citizen injuries. These fields already exist on the raw models.
property_damage = Sum of onsite and offsite property damage. Again, these fields already exist on the raw models.
"""
qs = raw_models.tblS6AccidentHistory.objects.values(
'AccidentHistoryID',
'FacilityID',
'AccidentDate',
'AccidentTime',
'NAICSCode',
'AccidentReleaseDuration',
'RE_Gas',
'RE_Spill',
'RE_Fire',
'RE_Explosion',
'RE_ReactiveIncident',
'RS_StorageVessel',
'RS_Piping',
'RS_ProcessVessel',
'RS_TransferHose',
'RS_Valve',
'RS_Pump',
'RS_Joint',
'OtherReleaseSource',
'WindSpeed',
'WindSpeedUnitCode',
'WindDirection',
'Temperature',
'StabilityClass',
'Precipitation',
'WeatherUnknown',
'DeathsWorkers',
'DeathsPublicResponders',
'DeathsPublic',
'InjuriesWorkers',
'InjuriesPublicResponders',
'InjuriesPublic',
'OnsitePropertyDamage',
'OffsiteDeaths',
'Hospitalization',
'MedicalTreatment',
'Evacuated',
'ShelteredInPlace',
'OffsitePropertyDamage',
'ED_Kills',
'ED_MinorDefoliation',
'ED_WaterContamination',
'ED_SoilContamination',
'ED_Other',
'InitiatingEvent',
'CF_EquipmentFailure',
'CF_HumanError',
'CF_ImproperProcedure',
'CF_Overpressurization',
'CF_UpsetCondition',
'CF_BypassCondition',
'CF_Maintenance',
'CF_ProcessDesignFailure',
'CF_UnsuitableEquipment',
'CF_UnusualWeather',
'CF_ManagementError',
'CF_Other',
'OffsiteRespondersNotify',
'CI_ImprovedEquipment',
'CI_RevisedMaintenance',
'CI_RevisedTraining',
'CI_RevisedOpProcedures',
'CI_NewProcessControls',
'CI_NewMitigationSystems',
'CI_RevisedERPlan',
'CI_ChangedProcess',
'CI_ReducedInventory',
'CI_None',
'CI_OtherType',
'CBI_Flag',
).annotate(
accident_id=F('AccidentHistoryID'),
rmp_id=F('FacilityID'),
accident_date=F('AccidentDate'),
accident_time=F('AccidentTime'),
naics_code=F('NAICSCode'),
release_duration=F('AccidentReleaseDuration'),
re_gas=F('RE_Gas'),
re_spill=F('RE_Spill'),
re_fire=F('RE_Fire'),
re_explosion=F('RE_Explosion'),
re_reactive_incident=F('RE_ReactiveIncident'),
rs_storage_vessel=F('RS_StorageVessel'),
rs_piping=F('RS_Piping'),
rs_process_vessel=F('RS_ProcessVessel'),
rs_transfer_hose=F('RS_TransferHose'),
rs_valve=F('RS_Valve'),
rs_pump=F('RS_Pump'),
rs_joint=F('RS_Joint'),
other_release_source=F('OtherReleaseSource'),
wind_speed=F('WindSpeed'),
wind_speed_unit=F('WindSpeedUnitCode'),
wind_direction=F('WindDirection'),
temperature=F('Temperature'),
stability_class=F('StabilityClass'),
precipitation=F('Precipitation'),
unknown_weather=F('WeatherUnknown'),
deaths_workers=F('DeathsWorkers'),
deaths_responders=F('DeathsPublicResponders'),
deaths_public=F('DeathsPublic'),
injuries_workers=F('InjuriesWorkers'),
injuries_responders=F('InjuriesPublicResponders'),
injuries_public=F('InjuriesPublic'),
onsite_damage=F('OnsitePropertyDamage'),
offsite_deaths=F('OffsiteDeaths'),
hospitalization=Cast('Hospitalization', CopyFromIntegerField()),
offsite_medical=Cast('MedicalTreatment', CopyFromIntegerField()),
offsite_evacuated=Cast('Evacuated', CopyFromIntegerField()),
offsite_shelter=Cast('ShelteredInPlace', CopyFromIntegerField()),
offsite_damage=F('OffsitePropertyDamage'),
ed_kills=F('ED_Kills'),
ed_defoliation=F('ED_MinorDefoliation'),
ed_water_contamination=F('ED_WaterContamination'),
ed_soil_contamination=F('ED_SoilContamination'),
ed_other=F('ED_Other'),
initiating_event=F('InitiatingEvent'),
cf_equipment_failure=F('CF_EquipmentFailure'),
cf_human_error=F('CF_HumanError'),
cf_improper_procedure=F('CF_ImproperProcedure'),
cf_overpressure=F('CF_Overpressurization'),
cf_upset_condition=F('CF_UpsetCondition'),
cf_bypass_condition=F('CF_BypassCondition'),
cf_maintenance=F('CF_Maintenance'),
cf_process_design_failure=F('CF_ProcessDesignFailure'),
cf_unsuitable_equipment=F('CF_UnsuitableEquipment'),
cf_unusual_weather=F('CF_UnusualWeather'),
cf_management_error=F('CF_ManagementError'),
cf_other=F('CF_Other'),
offsite_responders_notify=F('OffsiteRespondersNotify'),
ci_improved_equipment=F('CI_ImprovedEquipment'),
ci_revised_maintenance=F('CI_RevisedMaintenance'),
ci_revised_training=F('CI_RevisedTraining'),
ci_revised_op_procedures=F('CI_RevisedOpProcedures'),
ci_new_process_controls=F('CI_NewProcessControls'),
ci_new_mitigation_systems=F('CI_NewMitigationSystems'),
ci_response_plan=F('CI_RevisedERPlan'),
ci_changed_process=F('CI_ChangedProcess'),
ci_reduced_inventory=F('CI_ReducedInventory'),
ci_none=F('CI_None'),
ci_other=F('CI_OtherType'),
cbi_flag=F('CBI_Flag'),
num_acc_chem=Count('tbls6accidentchemicals'),
flam_total=Sum(
Case(When(tbls6accidentchemicals__ChemicalID__ChemType='F',
then=('tbls6accidentchemicals__QuantityReleased')),
default=Value(0))),
toxic_total=Sum(
Case(When(tbls6accidentchemicals__ChemicalID__ChemType='T',
then=('tbls6accidentchemicals__QuantityReleased')),
default=Value(0))),
quantity_total=F('flam_total') + F('toxic_total'),
num_deaths=F('DeathsWorkers') + F('DeathsPublicResponders') +
F('DeathsPublic'),
num_injuries=F('InjuriesPublic') + F('InjuriesWorkers') +
F('InjuriesPublicResponders'),
num_evacuated=F('Evacuated'),
property_damage=F('OnsitePropertyDamage') +
F('OffsitePropertyDamage'),
)
return qs
@property
def formatted_time(self):
try:
self._formatted_time
except AttributeError:
if self.accident_time == '':
self._formatted_time = 'unspecified time'
else:
self._formatted_time = '{0}:{1}'.format(
self.accident_time[0:2],
self.accident_time[2:4],
)
return self._formatted_time
@property
def formatted_duration(self):
try:
self._formatted_duration
except AttributeError:
if self.release_duration != '':
hrs = int(self.release_duration[0:3])
mins = int(self.release_duration[3:6])
if hrs > 0 and mins > 0:
self._formatted_duration = '{0} hours and {1} minutes'.format(
hrs, mins)
elif hrs > 0:
self._formatted_duration = '{0} hours'.format(hrs)
elif mins > 0:
self._formatted_duration = '{0} minutes'.format(mins)
else:
self._formatted_duration = self.release_duration
return self._formatted_duration
@property
def release_events(self):
try:
self._release_events
except AttributeError:
self._release_events = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('re_')
if self.__dict__[f.name]
]
return self._release_events
@property
def release_sources(self):
try:
self._release_sources
except AttributeError:
self._release_sources = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('rs_')
if self.__dict__[f.name]
]
if self.other_release_source != '':
self._release_sources.append(self.other_release_source)
return self._release_sources
@property
def environmental_damages(self):
try:
self._environmental_damages
except AttributeError:
self._environmental_damages = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('ed_')
if self.__dict__[f.name]
]
if self.ed_other != '':
self._environmental_damages.append(self.ed_other)
return self._environmental_damages
@property
def contributing_factors(self):
try:
self._contributing_factors
except AttributeError:
self._contributing_factors = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('cf_')
if self.__dict__[f.name]
]
if self.cf_other != '':
self._contributing_factors.append(self.cf_other)
return self._contributing_factors
@property
def changes_introduced(self):
try:
self._changes_introduced
except AttributeError:
self._changes_introduced = [
f.verbose_name
for f in self._meta.model.get_prefixed_boolean_fields('ci_')
if self.__dict__[f.name]
]
if self.ci_other != '':
self._changes_introduced.append(self.ci_other)
return self._changes_introduced
class Meta:
ordering = ['rmp_id', '-accident_date']
class AccChem(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='accchem_id',
verbose_name='Accident Chemical Record ID',
help_text='A unique ID for each accident chemical record.',
)
accident = CopyFromForeignKey(
'Accident',
on_delete=models.PROTECT,
help_text='The unique ID for each accident record',
)
chemical = CopyFromForeignKey(
'ChemCd',
on_delete=models.PROTECT,
help_text='The identifying ID for a particular chemical released in an '
'accident.',
)
quantity_lbs = CopyFromIntegerField(
null=True,
verbose_name='Amount Released (lbs)',
help_text='The amount of the substance released in the accident, in '
'pounds, to two significant digits.',
)
percent_weight = CopyFromDecimalField(
decimal_places=2,
null=True,
max_digits=5,
verbose_name='Percent Weight (Within Mixture)',
help_text='The percent weight of a chemical within a mixture released '
'in an accident.',
)
num_acc_flam = CopyFromIntegerField(
null=True,
verbose_name='Number of Flammable Components',
help_text='The number of listed flammable component chemicals for this'
' chemical record.',
)
cas = CopyFromCharField(
max_length=9,
verbose_name='CAS number',
help_text='The identifying CAS number for a chemical.',
)
CHEMICAL_TYPE_CHOICES = (
('T', 'toxic'),
('F', 'flammable'),
)
chemical_type = CopyFromCharField(
max_length=1,
choices=CHEMICAL_TYPE_CHOICES,
help_text='"The type of chemical.',
)
@classmethod
def get_transform_queryset(self):
"""
num_acc_fhem is calculated by getting the count of AccidentChemicalID from tblS6FlammableMixtureChemicals
"""
qs = raw_models.tblS6AccidentChemicals.objects.annotate(
accchem_id=F('AccidentChemicalID'),
accident_id=F('AccidentHistoryID'),
chemical_id=F('ChemicalID'),
quantity_lbs=F('QuantityReleased'),
percent_weight=F('PercentWeight'),
num_acc_flam=Count('tbls6flammablemixturechemicals'),
cas=F('ChemicalID__CASNumber'),
chemical_type=F('ChemicalID__ChemType'),
).order_by('accchem_id')
return qs
class ToxicsAltRelease(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='toxic_id',
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE,
)
percent_weight = CopyFromDecimalField(
max_digits=4,
decimal_places=1,
null=True,
)
physical_state = CopyFromCharField(max_length=1, blank=True)
analytical_basis = CopyFromCharField(max_length=255, blank=True)
scenario = CopyFromCharField(max_length=200)
quantity_released = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
source_column='quantity_lbs',
null=True,
)
release_duration = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
null=True,
)
release_rate = CopyFromBooleanField(null=True)
wind_speed = CopyFromDecimalField(
max_digits=6,
decimal_places=2,
null=True,
)
stability_class = CopyFromCharField(max_length=1, blank=True)
topography = CopyFromCharField(max_length=1, blank=True)
endpoint_distance = CopyFromDecimalField(
max_digits=5, decimal_places=1, null=True,
)
population = CopyFromCharField(
max_length=9, blank=True, verbose_name='Residential population'
)
pr_schools = CopyFromBooleanField()
pr_residences = CopyFromBooleanField()
pr_hospitals = CopyFromBooleanField()
pr_prisons = CopyFromBooleanField()
pr_public_recreation = CopyFromBooleanField(
source_column='pr_public_rec'
)
pr_comm_ind = CopyFromBooleanField()
pr_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='pr_othertype',
)
er_natl_state_parks = CopyFromBooleanField(
source_column='er_natlstateparks'
)
er_wildlife_sactuary = CopyFromBooleanField(
source_column='er_wildlifesanct'
)
er_fed_wilderness = CopyFromBooleanField(
source_column='er_fedwilderness'
)
er_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='er_othertype'
)
pm_dikes = CopyFromBooleanField()
pm_enclosures = CopyFromBooleanField()
pm_berms = CopyFromBooleanField()
pm_drains = CopyFromBooleanField()
pm_sumps = CopyFromBooleanField()
pm_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='pm_othertype'
)
am_sprinklers = CopyFromBooleanField()
am_deluge_systems = CopyFromBooleanField(
source_column='am_delugesystems'
)
am_watercurtain = CopyFromBooleanField()
am_neutralization = CopyFromBooleanField()
am_excess_flowvalve = CopyFromBooleanField(
source_column='am_excessflowvalve'
)
am_flares = CopyFromBooleanField()
am_scrubbers = CopyFromBooleanField()
am_emergency_shutdown = CopyFromBooleanField(
source_column='am_emerg_shutdown'
)
am_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='am_othertype'
)
ptrgraphic = CopyFromCharField(max_length=12, blank=True)
cbi_flag = CopyFromBooleanField()
source_file = 'rmp_alt_tox'
class Registration(BaseRMPModel):
rmp_id = CopyFromIntegerField(
primary_key=True,
db_column='rmp_id',
)
facility_name = CopyFromCharField(max_length=255, blank=True)
street_1 = CopyFromCharField(max_length=35, blank=True)
street_2 = CopyFromCharField(max_length=35, blank=True)
city = CopyFromCharField(max_length=19, blank=True)
state = CopyFromForeignKey(
'StateCd',
on_delete=models.PROTECT,
blank=True,
db_column='state',
)
zip = CopyFromCharField(max_length=5, blank=True)
zip_ext = CopyFromCharField(max_length=4, blank=True)
county_fips = CopyFromForeignKey(
'CountyCd',
on_delete=models.PROTECT,
db_column='county_fips',
null=True,
)
county_name = CopyFromCharField(max_length=50, null=True)
lepc = CopyFromCharField(max_length=30, blank=True)
latitude_dec = CopyFromCharField(max_length=10, blank=True)
longitude_dec = CopyFromCharField(max_length=11, blank=True)
valid_latlong = CopyFromCharField(max_length=1, blank=True)
latlong_meth = CopyFromForeignKey(
'LlmethCd',
on_delete=models.PROTECT,
db_column='latlong_meth',
null=True,
)
latlong_desc = CopyFromForeignKey(
'LldescCd',
on_delete=models.PROTECT,
db_column='latlong_desc',
null=True,
)
facility_url = CopyFromCharField(max_length=100, blank=True)
facility_phone = CopyFromCharField(max_length=10, blank=True)
facility_email = CopyFromCharField(max_length=100, blank=True)
facility_duns = CopyFromCharField(max_length=9, blank=True)
facility_email = CopyFromCharField(max_length=100, blank=True)
parent = CopyFromCharField(max_length=250, blank=True)
parent_2 = CopyFromCharField(max_length=50, blank=True)
parent_duns = CopyFromCharField(max_length=9, blank=True)
parent2_duns = CopyFromCharField(max_length=9, blank=True)
operator_name = CopyFromCharField(max_length=250, blank=True)
operator_phone = CopyFromCharField(max_length=10, blank=True)
op_street_1 = CopyFromCharField(max_length=35, blank=True)
op_street_2 = CopyFromCharField(max_length=35, blank=True)
operator_city = CopyFromCharField(max_length=19, blank=True)
operator_state = CopyFromForeignKey(
'StateCd',
on_delete=models.PROTECT,
blank=True,
related_name='+',
db_column='operator_state',
)
operator_zip = CopyFromCharField(max_length=5, blank=True)
operator_zip_ext = CopyFromCharField(max_length=4, blank=True)
rmp_contact = CopyFromCharField(max_length=35, blank=True)
rmp_contact_title = CopyFromCharField(max_length=250, blank=True)
em_contact_name = CopyFromCharField(max_length=250, blank=True)
em_contact_title = CopyFromCharField(max_length=35, blank=True)
em_contact_phone = CopyFromCharField(max_length=10, blank=True)
phone_24hour = CopyFromCharField(max_length=10, blank=True)
phone_24hour_ext = CopyFromCharField(max_length=10, blank=True)
num_fte = CopyFromIntegerField(null=True)
other_facility_id = CopyFromCharField(blank=True, max_length=15)
facility_id = CopyFromForeignKey(
'Facility',
db_column='facility_id',
on_delete=models.PROTECT,
)
osha_psm_yn = CopyFromBooleanField()
epcra_302_yn = CopyFromBooleanField()
caa_title_v_yn = CopyFromBooleanField()
caa_permit_id = CopyFromCharField(blank=True, max_length=15)
safety_inspect_dt = CopyFromDateTimeField(null=True)
safety_inspect_by = CopyFromCharField(max_length=50, blank=True)
osha_ranking = CopyFromBooleanField()
predictive_file_yn = CopyFromBooleanField()
submission_type = CopyFromCharField(
max_length=1,
choices=choices.SUBMISSION_TYPES,
blank=True,
)
rmp_desc = CopyFromCharField(max_length=50, blank=True)
no_accidents_yn = CopyFromBooleanField()
foreign_province = CopyFromCharField(max_length=35, blank=True)
foreign_zip = CopyFromCharField(max_length=14, blank=True)
foreign_country = CopyFromCharField(max_length=2, blank=True)
num_fte_cbi_flag = CopyFromBooleanField()
# complete_check_dt = CopyFromDateTimeField(blank=True, null=True)
complete_check_dt = CopyFromCharField(blank=True, max_length=10)
# error_report_dt = CopyFromDateTimeField(blank=True, null=True)
error_report_dt = CopyFromCharField(blank=True, max_length=10)
receipt_date = CopyFromDateTimeField(blank=True)
graphics_ind = CopyFromBooleanField()
attachment_ind = CopyFromBooleanField()
cert_rec_flag = CopyFromBooleanField()
submit_method = CopyFromCharField(max_length=50, blank=True)
cbi_substant_flag = CopyFromBooleanField()
elect_waiver_flag = CopyFromBooleanField()
postmark_date = CopyFromCharField(max_length=25, blank=True)
rmp_complete_flag = CopyFromCharField(max_length=1, blank=True)
# deregistration_dt = CopyFromDateTimeField(blank=True, null=True)
deregistration_dt = CopyFromCharField(blank=True, max_length=10)
# dereg_effect_dt = CopyFromDateTimeField(blank=True, null=True)
dereg_effect_dt = CopyFromCharField(blank=True, max_length=10)
# anniversary_date = CopyFromDateTimeField(blank=True, null=True)
anniversary_date = CopyFromCharField(blank=True, max_length=10)
cbi_flag = CopyFromBooleanField()
unsanitized_vers = CopyFromBooleanField()
version_number = CopyFromCharField(max_length=15, blank=True)
frs_lat_dec = CopyFromDecimalField(max_digits=5,
decimal_places=2,
null=True)
frs_long_dec = CopyFromDecimalField(max_digits=5,
decimal_places=2,
null=True)
frs_ll_desc = CopyFromCharField(max_length=40, blank=True)
frs_ll_method = CopyFromCharField(max_length=60, blank=True)
hor_measure = CopyFromCharField(max_length=6, blank=True)
hor_ref = CopyFromCharField(
max_length=3,
blank=True,
choices=choices.HORIZONTAL_DATUM_CODES,
)
source_scale = CopyFromCharField(max_length=10, blank=True)
em_email = CopyFromCharField(max_length=100, blank=True)
prep_name = CopyFromCharField(max_length=70, blank=True)
prep_street_1 = CopyFromCharField(max_length=35, blank=True)
prep_street_2 = CopyFromCharField(max_length=35, blank=True)
prep_city = CopyFromCharField(max_length=19, blank=True)
prep_state = CopyFromForeignKey(
'StateCd',
on_delete=models.PROTECT,
blank=True,
related_name='+',
db_column='prep_state',
)
prep_zip = CopyFromCharField(max_length=5, blank=True)
prep_zip_ext = CopyFromCharField(max_length=4, blank=True)
prep_phone = CopyFromCharField(max_length=10, blank=True)
prep_foreign_state = CopyFromCharField(max_length=35, blank=True)
prep_country = CopyFromCharField(max_length=2, blank=True)
prep_foreign_zip = CopyFromCharField(max_length=14, blank=True)
sub_reason = CopyFromCharField(max_length=3, blank=True)
rmp_email = CopyFromCharField(max_length=100, blank=True)
dereg_reason = CopyFromCharField(max_length=2, blank=True)
dereg_other = CopyFromCharField(max_length=80, blank=True)
num_accident_records = CopyFromIntegerField(null=True)
num_accident_actual = CopyFromIntegerField(null=True)
num_accident_divider = CopyFromIntegerField(null=True)
num_facility = CopyFromIntegerField(null=True)
num_process = CopyFromIntegerField(null=True)
num_proc_chem = CopyFromIntegerField(null=True)
num_acc_chem = CopyFromIntegerField(null=True)
num_proc_chem_tox = CopyFromIntegerField(null=True)
num_proc_chem_flam = CopyFromIntegerField(null=True)
num_response = CopyFromIntegerField(null=True)
num_chem_real = CopyFromIntegerField(null=True)
num_worst_tox = CopyFromIntegerField(null=True)
num_alt_tox = CopyFromIntegerField(null=True)
num_worst_flam = CopyFromIntegerField(null=True)
num_alt_flam = CopyFromIntegerField(null=True)
num_prev_2 = CopyFromIntegerField(null=True)
num_prev_3 = CopyFromIntegerField(null=True)
acc_flam_tot = CopyFromBigIntegerField(null=True)
acc_toxic_tot = CopyFromBigIntegerField(null=True)
acc_quantity_tot = CopyFromBigIntegerField(null=True)
num_deaths = CopyFromIntegerField(null=True)
num_injuries = CopyFromIntegerField(null=True)
num_evacuated = CopyFromIntegerField(null=True)
property_damage = CopyFromBigIntegerField(null=True)
foreign_country_tr = CopyFromCharField(max_length=60,
blank=True,
null=True)
num_execsum = CopyFromIntegerField(null=True)
@classmethod
def get_transform_queryset(self):
qs = raw_models.tblS1Facilities.objects.select_related(
'FacilityCountyFIPS'
).values('FacilityID', ).annotate(
rmp_id=F('FacilityID'),
facility_name=F('FacilityName'),
street_1=F('FacilityStr1'),
street_2=F('FacilityStr2'),
city=F('FacilityCity'),
state=F('FacilityState'),
zip=F('FacilityZipCode'),
zip_ext=F('Facility4DigitZipExt'),
county_fips=F('FacilityCountyFIPS'),
county_name=F('FacilityCountyFIPS__County_Name'),
lepc=F('LEPC'),
latitude_dec=F('FacilityLatDecDegs'),
longitude_dec=F('FacilityLongDecDegs'),
valid_latlong=F('ValidLatLongFlag'),
latlong_meth=F('LatLongMethod'),
latlong_desc=F('LatLongDescription'),
facility_url=F('FacilityURL'),
facility_phone=F('FacilityPhoneNumber'),
facility_email=F('FacilityEmailAddress'),
facility_duns=F('FacilityDUNS'),
parent=F('ParentCompanyName'),
parent_2=F('Company2Name'),
parent_duns=F('CompanyDUNS'),
parent2_duns=F('Company2DUNS'),
operator_name=F('OperatorName'),
operator_phone=F('OperatorPhone'),
op_street_1=F('OperatorStr1'),
op_street_2=F('OperatorStr2'),
operator_city=F('OperatorCity'),
operator_state=F('OperatorStateFIPS'),
operator_zip=F('OperatorZipCode'),
operator_zip_ext=F('OperatorZipCodeExt'),
rmp_contact=F('RMPContact'),
rmp_contact_title=F('RMPTitle'),
em_contact_name=F('EmergencyContactName'),
em_contact_title=F('EmergencyContactTitle'),
em_contact_phone=F('EmergencyContactPhone'),
phone_24hour=F('Phone24'),
phone_24hour_ext=F('EmergencyContactExt_PIN'),
num_fte=F('FTE'),
other_facility_id=F('OtherEPAFacilityID'),
facility_id=F('EPAFacilityID'),
osha_psm_yn=F('OSHA_PSM'),
epcra_302_yn=F('EPCRA_302'),
caa_title_v_yn=F('CAA_TitleV'),
caa_permit_id=F('ClearAirOpPermitID'),
safety_inspect_dt=F('SafetyInspectionDate'),
safety_inspect_by=F('SafetyInspectionBy'),
osha_ranking=F('OSHARanking'),
predictive_file_yn=F('PredictiveFilingFlag'),
submission_type=F('SubmissionType'),
rmp_desc=F('RMPDescription'),
no_accidents_yn=F('NoAccidents'),
foreign_province=F('ForeignStateProv'),
foreign_zip=F('ForeignZipCode'),
foreign_country=F('ForeignCountry'),
num_fte_cbi_flag=F('CBI_Flag'),
complete_check_dt=F('CompletionCheckDate'),
error_report_dt=F('ErrorReportDate'),
receipt_date=F('ReceiptDate'),
graphics_ind=F('GraphicsIndicator'),
attachment_ind=F('AttachmentsIndicator'),
cert_rec_flag=F('CertificationReceivedFlag'),
submit_method=F('SubmissionMethod'),
cbi_substant_flag=F('CBISubstantiationFlag'),
elect_waiver_flag=F('ElectronicWaiverReceivedFlag'),
postmark_date=F('PostmarkDate'),
rmp_complete_flag=F('RMPCompleteFlag'),
deregistration_dt=F('DeRegistrationDate'),
dereg_effect_dt=F('DeRegistrationEffectiveDate'),
anniversary_date=F('AnniversaryDate'),
cbi_flag=F('CBIFlag'),
unsanitized_vers=F('CBIUnsanitizedVersionFlag'),
version_number=F('VersionNumber'),
frs_lat_dec=F('FRS_Lat'),
frs_long_dec=F('FRS_Long'),
frs_ll_desc=F('FRS_Description'),
frs_ll_method=F('FRS_Method'),
hor_measure=F('HorizontalAccMeasure'),
hor_ref=F('HorizontalRefDatumCode'),
source_scale=F('SourceMapScaleNumber'),
em_email=F('EmergencyContactEmail'),
prep_name=F('RMPPreparerName'),
prep_street_1=F('RMPPreparerStreet1'),
prep_street_2=F('RMPPreparerStreet2'),
prep_city=F('RMPPreparerCity'),
prep_state=F('RMPPreparerState'),
prep_zip=F('RMPPreparerZIP'),
prep_zip_ext=F('RMPPreparerZIP4Ext'),
prep_phone=F('RMPPreparerTelephone'),
prep_foreign_state=F('RMPPreparerForeignStateOrProvince'),
prep_country=F('RMPPreparerForeignCountry'),
prep_foreign_zip=F('RMPPreparerForeignPostalCode'),
sub_reason=F('RMPSubmissionReasonCode'),
rmp_email=F('RMPEmail'),
dereg_reason=F('DeregistrationReasonCode'),
dereg_other=F('DeregistrationReasonOtherText'),
num_accident_records=Count('tbls6accidenthistory', ),
num_accident_actual=Count(
'tbls6accidenthistory',
distinct=True,
),
num_accident_divider=Case(When(num_accident_actual=0, then=1),
default=F('num_accident_records') /
F('num_accident_actual')),
num_facility=Count(
'tblfacility',
distinct=True,
),
num_process=Count(
'tbls1processes',
distinct=True,
),
num_proc_chem=Count('tbls1processes__tbls1processchemicals'),
num_acc_chem=Count('tbls6accidenthistory__tbls6accidentchemicals'),
num_response=Count('tbls9emergencyresponses', distinct=True),
num_chem_real=Count(Case(
When(
tbls1processes__tbls1processchemicals__ChemicalID__gt=Value(
0),
then=('tbls1processes__tbls1processchemicals'))),
distinct=True),
num_proc_chem_tox=Round(
(F('num_proc_chem') / F('num_chem_real')) / 2),
num_proc_chem_flam=Round(
(F('num_proc_chem') / F('num_chem_real')) / 2),
num_worst_tox=Count(
'tbls1processes__tbls1processchemicals__tbls2toxicsworstcase__ProcessChemicalID',
distinct=True),
num_alt_tox=Count(
'tbls1processes__tbls1processchemicals__tbls3toxicsaltreleases__ProcessChemicalID',
distinct=True),
num_worst_flam=Count(
'tbls1processes__tbls1processchemicals__tbls4flammablesworstcase',
distinct=True),
num_alt_flam=Count(
'tbls1processes__tbls1processchemicals__tbls5flammablesaltreleases',
distinct=True),
num_prev_2=Count(
'tbls1processes__tbls1process_naics__tbls8preventionprogram2',
distinct=True),
num_prev_3=Count(
'tbls1processes__tbls1process_naics__tbls7preventionprogram3',
distinct=True),
acc_flam_tot=Round(
Sum(
Case(When(
tbls6accidenthistory__tbls6accidentchemicals__ChemicalID__ChemType
='F',
then=
('tbls6accidenthistory__tbls6accidentchemicals__QuantityReleased'
)),
default=Value(0),
output_field=CopyFromIntegerField())) /
F('num_accident_divider')),
acc_toxic_tot=Round(
Sum(
Case(When(
tbls6accidenthistory__tbls6accidentchemicals__ChemicalID__ChemType
='T',
then=
('tbls6accidenthistory__tbls6accidentchemicals__QuantityReleased'
)),
default=Value(0),
output_field=CopyFromIntegerField())) /
F('num_accident_divider')),
acc_quantity_tot=F('acc_flam_tot') + F('acc_toxic_tot'),
num_deaths=Round(
Sum(F('tbls6accidenthistory__DeathsWorkers') +
F('tbls6accidenthistory__DeathsPublicResponders') +
F('tbls6accidenthistory__DeathsPublic'),
default=Value(0),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
num_injuries=Round(
Sum(F('tbls6accidenthistory__InjuriesPublic') +
F('tbls6accidenthistory__InjuriesWorkers') +
F('tbls6accidenthistory__InjuriesPublicResponders'),
default=Value(0),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
num_evacuated=Round(
Sum(F('tbls6accidenthistory__Evacuated'),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
property_damage=Round(
Sum(F('tbls6accidenthistory__OnsitePropertyDamage') +
F('tbls6accidenthistory__OffsitePropertyDamage'),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
foreign_country_tr=F('ForeignCountry'),
num_execsum=Count('tblexecutivesummaries'),
)
return qs
class FlammablesAltRelease(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='flammable_id'
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE,
)
analytical_basis = CopyFromCharField(max_length=255, blank=True)
scenario = CopyFromCharField(max_length=200)
quantity_released = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
source_column='quantity_lbs',
null=True,
)
endpoint_used = CopyFromCharField(max_length=30, blank=True)
lfl_value = CopyFromDecimalField(
max_digits=5, decimal_places=1, null=True,
)
endpoint_distance = CopyFromDecimalField(
max_digits=5, decimal_places=1, null=True,
)
population = CopyFromCharField(
max_length=9, blank=True, verbose_name='Residential population',
)
pr_schools = CopyFromBooleanField()
pr_residences = CopyFromBooleanField()
pr_hospitals = CopyFromBooleanField()
pr_prisons = CopyFromBooleanField()
pr_public_recreation = CopyFromBooleanField(
source_column='pr_public_rec'
)
pr_comm_ind = CopyFromBooleanField()
pr_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='pr_othertype',
)
er_natl_state_parks = CopyFromBooleanField(
source_column='er_natlstateparks'
)
er_wildlife_sactuary = CopyFromBooleanField(
source_column='er_wildlifesanct'
)
er_fed_wilderness = CopyFromBooleanField(
source_column='er_fedwilderness'
)
er_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='er_othertype'
)
pm_dikes = CopyFromBooleanField()
pm_firewalls = CopyFromBooleanField()
pm_blastwalls = CopyFromBooleanField()
pm_enclosures = CopyFromBooleanField()
pm_other_type = CopyFromCharField(
source_column='pm_othertype', max_length=200, blank=True
)
am_sprinklers = CopyFromBooleanField()
am_deluge_systems = CopyFromBooleanField(
source_column='am_delugesystems'
)
am_watercurtain = CopyFromBooleanField()
am_excess_flowvalve = CopyFromBooleanField(
source_column='am_excessflowvalve'
)
am_other_type = CopyFromCharField(
max_length=200, blank=True, source_column='am_othertype'
)
ptrgraphic = CopyFromCharField(max_length=12, blank=True)
cbi_flag = CopyFromBooleanField()
source_file = 'rmp_alt_flam'
class FlammablesAltRelease(BaseRMPModel):
flammable_id = CopyFromIntegerField(
primary_key=True,
source_column='flammable_id'
)
procchem = CopyFromForeignKey(
'ProcChem',
on_delete=models.CASCADE,
source_column='process_chemical_id'
)
analytical_basis = CopyFromCharField(max_length=255, blank=True)
scenario = CopyFromCharField(max_length=200)
quantity_released = CopyFromDecimalField(
max_digits=5,
decimal_places=2,
# source_column='quantity_lbs',
null=True,
)
endpoint_used = CopyFromCharField(max_length=30, blank=True)
lfl_value = CopyFromDecimalField(
max_digits=5, decimal_places=1, null=True,
)
endpoint_distance = CopyFromDecimalField(
source_column="distance2_endpoint",
max_digits=5,
decimal_places=1,
null=True,
)
population = CopyFromCharField(
source_column="residential_population",
max_length=9,
blank=True,
verbose_name='Residential population',
)
pr_schools = CopyFromBooleanField()
pr_residences = CopyFromBooleanField()
pr_hospitals = CopyFromBooleanField()
pr_prisons = CopyFromBooleanField()
pr_public_recreation = CopyFromBooleanField()
pr_comm_ind = CopyFromBooleanField()
pr_other_type = CopyFromCharField(
max_length=200, blank=True
)
er_natl_state_parks = CopyFromBooleanField()
er_wildlife_sactuary = CopyFromBooleanField()
er_fed_wilderness = CopyFromBooleanField()
er_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
pm_dikes = CopyFromBooleanField()
pm_firewalls = CopyFromBooleanField(source_column='pm_fire_walls')
pm_blastwalls = CopyFromBooleanField(source_column='pm_blast_walls')
pm_enclosures = CopyFromBooleanField()
pm_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
am_sprinklers = CopyFromBooleanField(source_column='am_sprinkler_systems')
am_deluge_systems = CopyFromBooleanField()
am_watercurtain = CopyFromBooleanField(source_column='am_water_curtain')
am_excess_flowvalve = CopyFromBooleanField(source_column='am_excess_flow_valve')
am_other_type = CopyFromCharField(
max_length=200,
blank=True,
)
ptrgraphic = CopyFromCharField(
source_column='ptr_graphic',
max_length=12,
blank=True,
)
cbi_flag = CopyFromBooleanField()
@classmethod
def get_transform_queryset(self):
m = raw_models.tblS5FlammablesAltReleases
return m.objects.get_default_transform_queryset()
class tblS1Facilities(BaseRMPModel):
FacilityID = CopyFromIntegerField(
primary_key=True,
verbose_name='RMP Identifier',
help_text='Unique identifier for all RMPs submitted by a specific '
'facility (assigned by Reporting Center for first-time '
'submission). * After this number is assigned to the first '
'submission for a facility, subsequent submissions for the '
'same facility must include this identifier.',
)
FacilityName = CopyFromCharField(
max_length=255,
blank=True,
verbose_name='1.1.a Facility Name',
help_text='Facility name specific to the site.',
)
FacilityStr1 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.5.a Facility Street - Line 1',
help_text='Facility Street - Line 1 using local street and road '
'designations. No post office box numbers or rural route '
'numbers. This is not the mailing address.',
)
FacilityStr2 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.5.b Facility Street - Line 2',
help_text='Facility Street Address - Line 2',
)
FacilityCity = CopyFromCharField(
max_length=19,
verbose_name='1.5.c Facility City',
help_text='The name of the city, town, or village where the '
'facility is located.',
)
FacilityState = CopyFromForeignKey(
'tlkpStateFIPSCodes',
to_field='STATE_ABBR',
db_column='FacilityState',
on_delete=models.PROTECT,
related_name='facilitystate',
verbose_name='1.5.d Facility State',
help_text='The U.S. Postal Service abbreviation for the state in '
'which the facility is located.',
)
FacilityZipCode = CopyFromCharField(
max_length=5,
verbose_name='1.5.e Facility ZIP Code',
help_text='The Zoning Improvement Plan (ZIP) Code assigned to the '
'facility by the U.S. Postal Service which represents a '
'geographic area that facilitates mail delivery.',
)
Facility4DigitZipExt = CopyFromCharField(
max_length=4,
blank=True,
verbose_name='1.5.e Facility ZIP Extention',
help_text='The four-digit extension code that represents the '
'geographic segment that is a sub-unit of the ZIP Code and '
'further refines the exact location of the facility.',
)
FacilityCountyFIPS = CopyFromForeignKey(
'tlkpCountyFIPSCodes',
to_field='StateCounty_Code',
db_column='FacilityCountyFIPS',
on_delete=models.PROTECT,
blank=True,
verbose_name='1.5.f Facility County',
help_text='Federal Information Processing Standard (FIPS) code for '
'county in which the facility is located.',
)
LEPC = CopyFromCharField(
max_length=30,
blank=True,
verbose_name='1.10 LEPC',
help_text='Local Emergency Planning Committee (LEPC) associated with '
'the facility county. For LEPC information refer to the '
'LEPC/SERC Net Web site at http://www.RTK.NET:80/lepc. Must '
'cover all or part of the Facility County.',
)
FacilityLatDecDegs = CopyFromDecimalField(
max_digits=9,
decimal_places=7,
verbose_name='1.5.g Facility Latitude (Decimal Degrees)',
help_text='Facility Latitude in decimal degrees.',
)
FacilityLongDecDegs = CopyFromDecimalField(
max_digits=10,
decimal_places=7,
verbose_name='1.5.h Facility Longitude (Decimal Degrees)',
help_text='Facility Longitude in decimal degrees.',
)
ValidLatLongFlag = CopyFromCharField(
max_length=1,
blank=True,
source_column='ValidLatLongFlag',
verbose_name='Valid Lat/Long Flag',
help_text='Flag used to indicate whether the Latitude/Longitude is '
'valid.',
)
LatLongMethod = CopyFromForeignKey(
'tlkpLatLongMethods',
to_field='Method_Code',
on_delete=models.PROTECT,
blank=True,
source_column='LatLongMethod',
verbose_name='1.5.i Lat/Long Method',
help_text='Code representing method used to obtain latitude or '
'longitude data. Codes can be obtained from Method '
'Accuracy Description (MAD) Version 6.1 Information Coding '
'Standards as implemented in Envirofacts Locational '
'Reference Tables (EF LRT). '
'http://www.epa.gov/enviro/html/lrt/lrt_over.html',
)
LatLongDescription = CopyFromCharField(
max_length=2,
blank=True,
verbose_name='1.5.j Lat/Long Description',
help_text='Code for the physical place corresponding to the coordinate.'
' Codes can be obtained from MAD Version 6.1 Standard as '
'implemented in Envirofacts Locational Reference Tables (EF '
'LRT). http://www.epa.gov/enviro/html/lrt/lrt_over.html',
)
FacilityURL = CopyFromURLField(
max_length=100,
blank=True,
verbose_name='1.9.c Facility or Parent Company WWW Homepage Address',
help_text='Facility or Parent Company homepage web address.',
)
FacilityPhoneNumber = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='1.9.b Facility Public Contact Phone Number',
help_text='Facility phone number for public inquiries to contact '
'owner, 112(r) person responsible, etc.',
)
FacilityEmailAddress = CopyFromEmailField(
max_length=100,
blank=True,
verbose_name='1.9.a Facility or Parent Company E-mail Address',
help_text='The text that represents the electronic mail (email) '
'address for the facility or parent company.',
)
FacilityDUNS = CopyFromCharField(
max_length=9,
verbose_name='1.4.a Facility DUNS',
help_text='The Data Universal Numbering System (DUNS) number assigned '
'by Dun & Bradstreet to the facility.',
)
ParentCompanyName = CopyFromCharField(
max_length=250,
blank=True,
verbose_name='1.1.b Parent Company #1 Name',
help_text='First Parent Company Name.',
)
Company2Name = CopyFromCharField(
max_length=50,
blank=True,
verbose_name='1.1.c Parent Company #2 Name',
help_text='Second Parent Company name for joint ventures.',
)
CompanyDUNS = CopyFromCharField(
max_length=9,
verbose_name='1.4.b Parent Company #1 DUNS',
help_text='The DUNS Number assigned by Dun & Bradstreet to the parent '
'of the company of interest.',
)
Company2DUNS = CopyFromCharField(
max_length=9,
verbose_name='1.4.c Parent Company #2 DUNS',
help_text='If your facility is owned by a joint venture, this is the '
'DUNS Number assigned by Dun & Bradstreet to the second '
'parent company.',
)
OperatorName = CopyFromCharField(
max_length=250,
blank=True,
verbose_name='1.6.a Owner/Operator Name',
help_text='Name of the person or entity that owns or operates the '
'facility.',
)
OperatorPhone = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='1.6.b Owner/Operator Phone',
help_text='Phone number for the Owner or Operator.',
)
OperatorStr1 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.6.c Owner/Operator Street - Line 1',
help_text='Line 1 of the business street mailing address for the '
'Owner or Operator.',
)
OperatorStr2 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.6.d Owner/Operator Street - Line 2',
help_text='Line 2 of the business street mailing address for the '
'Owner or Operator.',
)
OperatorCity = CopyFromCharField(
max_length=19,
blank=True,
verbose_name='1.6.e Owner/Operator City',
help_text='City for the business mailing address for the Owner or '
'Operator.',
)
OperatorStateFIPS = CopyFromForeignKey(
'tlkpStateFIPSCodes',
db_column='OperatorStateFIPS',
on_delete=models.PROTECT,
blank=True,
verbose_name='1.6.f Owner/Operator State',
help_text='The U.S. Postal Service state abbreviation for the address '
'of the Owner or Operator.',
)
OperatorZipCode = CopyFromCharField(
max_length=5,
blank=True,
verbose_name='1.6.g Owner/Operator ZIP Code',
help_text='ZIP Code for the business mailing address of the Owner or '
'Operator.',
)
OperatorZipCodeExt = CopyFromCharField(
max_length=4,
blank=True,
verbose_name='1.6.g Owner/Operator ZIP four-digit extention code',
help_text='The four-digit extension code that represents the '
'geographic segment that is a subunit of the ZIP Code and '
'further refines the business mailing address of the Owner '
'or Operator.',
)
RMPContact = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.7.a Name of Person Responsible for RMP Implementaion',
help_text='Person or position responsible for RMP implementation '
'(40 CFR Part 68).',
)
RMPTitle = CopyFromCharField(
max_length=250,
blank=True,
verbose_name='1.7.b Title/Position of Person Responsible for RMP '
'Implementaion',
help_text='Title of person or position responsible for RMP '
'implementation (40 CFR Part 68).',
)
EmergencyContactName = CopyFromCharField(
max_length=250,
blank=True,
verbose_name='1.8.a Emergency Contact Name',
help_text='Name of person designated as the emergency contact for the '
'facility.',
)
EmergencyContactTitle = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.8.b Emergency Contact Title',
help_text='Title or job classification of the emergency contact.',
)
EmergencyContactPhone = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='1.8.c Emergency Contact Phone',
help_text='Phone number where the emergency contact can be reached '
'during normal working hours.',
)
Phone24 = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='1.8.d 24-Hour Phone',
help_text='Number where emergency contact can be reached during '
'non-working hours, such as a beeper number.',
)
EmergencyContactExt_PIN = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='1.8.e 24-Hour Phone Extention/PIN',
help_text='Phone extension or pager number for the 24-Hour Phone.',
)
FTE = CopyFromIntegerField(
null=True,
verbose_name='1.11 Number of Full Time Employees (FTEs)',
help_text='Number of full-time equivalent employees.',
)
OtherEPAFacilityID = CopyFromCharField(
max_length=15,
null=True,
verbose_name='1.3 Other EPA Systems Program Facility Identifier',
help_text='The unique identification number assigned to a facility by '
'the Facility Index System (FINDS) (or if not known, the '
'Resource Conservation and Recovery Act (RCRA), Emergency '
'Planning and Community Right-to-Know Act (EPCRA), TRI, or '
'other EPA facility identifier).',
)
EPAFacilityID = CopyFromForeignKey(
'tblFacility',
db_column='EPAFacilityID',
on_delete=models.PROTECT,
verbose_name='1.2 EPA Facility Identifier',
help_text='1.2 EPA Facility Identifier',
)
OSHA_PSM = CopyFromBooleanField(
verbose_name='1.12.a Covered by: OSHA PSM',
help_text='Occupational Safety and Health Act (OSHA) Process Safety '
'Management (PSM) Standard. Question covers all processes '
'at the facility; if any process at the facility is '
'subject to OSHA PSM, must answer “Y” even if the PSM '
'process is not covered by this Rule.',
)
EPCRA_302 = CopyFromBooleanField(
verbose_name='1.12.b Covered by: EPCRA 302',
help_text='EPCRA Section 302 pertains to the Extremely Hazardous '
'Substances list. Any facility with a toxic regulated '
'substance above the threshold quantity in a process is '
'subject to EPCRA 302. If the facility is covered for only '
'flammable regulated substances, the facility is not '
'subject to 40 CFR 355 for those substances, although the '
'facility may be for toxic substances not affected by this '
'Rule.',
)
CAA_TitleV = CopyFromBooleanField(
verbose_name='1.12.c CAA Title V',
help_text='Indicate if your facility has a CAA Title V Operating '
'Permit with “Y.” CAA Title V Air Operating Permit ID '
'Title V (Part 70) of the Clean Air Act (40CFR70) '
'requires major sources of air pollution to obtain '
'permits.',
)
ClearAirOpPermitID = CopyFromCharField(
max_length=15,
null=True,
verbose_name='1.12.d Air Operating Permit ID',
help_text='Unique identifier for a CAA Title V Air Operating Permit '
'or state equivalent ID.',
)
SafetyInspectionDate = CopyFromDateTimeField(
null=True,
verbose_name='1.12.d Air Operating Permit ID',
help_text='Unique identifier for a CAA Title V Air Operating Permit '
'or state equivalent ID.',
)
SafetyInspectionBy = CopyFromCharField(
max_length=50,
blank=True,
verbose_name='1.15 Last Safety Inspection Performed by:',
help_text='A designation representing the external agency that '
'performed the last safety inspection. One or more of the '
'following is expected: OSHA State OSHA EPA State EPA Fire '
'Department Never had a safety inspection Other',
)
OSHARanking = CopyFromBooleanField(
verbose_name='1.13 OSH Star or Merit Ranking',
help_text="A stationary source with a Star or Merit ranking under "
"OSHA's voluntary protection program shall be exempt from "
"audits under paragraph (b)(2) and (b)(7) of [Section 68.220"
" - audits].",
)
PredictiveFilingFlag = CopyFromBooleanField(
verbose_name='Predictive Filing Flag',
help_text="An indication that the submitter is using Predictive "
"Filing for the facility's RMP.",
)
SubmissionType = CopyFromCharField(
max_length=1,
blank=True,
verbose_name='Submission Type',
help_text='Submission Type “F” - First-time submission “R” - '
'Resubmission “C” - Correction of existing RMP',
)
RMPDescription = CopyFromCharField(
max_length=50,
blank=True,
verbose_name='RMP Description',
help_text='RMP Description is an optional description for the whole '
'RMP. RMP Description is not accessible to RMP*Info on the '
'Web.',
)
NoAccidents = CopyFromBooleanField(
verbose_name='No Accidents Flag',
help_text='Optional Flag to Indicate whether there are any accidents '
'to report.',
)
ForeignStateProv = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='1.6.f Foreign State or Province',
help_text='If the Owner or Operator (reported in 1.6.a) has an '
'address outside the USA as his or her primary mailing '
'address, enter the name of the foreign state or province. '
'If the primary address is in the USA, or if there is no '
'state or province in the foreign mailing address, leave '
'this field blank.',
)
ForeignZipCode = CopyFromCharField(
max_length=14,
blank=True,
verbose_name='',
help_text='',
)
ForeignCountry = CopyFromForeignKey(
'tlkpForeignCountry',
db_column='ForeignCountry',
on_delete=models.PROTECT,
related_name='foreigncountry',
blank=True,
verbose_name='',
help_text='',
)
CBI_Flag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
CompletionCheckDate = CopyFromDateTimeField(
verbose_name='',
help_text='',
)
ErrorReportDate = CopyFromDateTimeField(
null=True,
verbose_name='',
help_text='',
)
ReceiptDate = CopyFromCharField(
max_length=25,
verbose_name='',
help_text='',
)
GraphicsIndicator = CopyFromBooleanField(
verbose_name='',
help_text='',
)
AttachmentsIndicator = CopyFromBooleanField(
verbose_name='',
help_text='',
)
CertificationReceivedFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
SubmissionMethod = CopyFromCharField(
max_length=50,
blank=True,
verbose_name='',
help_text='',
)
CBISubstantiationFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
ElectronicWaiverReceivedFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
PostmarkDate = CopyFromDateTimeField(
null=True,
verbose_name='',
help_text='',
)
RMPCompleteFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
DeRegistrationDate = CopyFromDateTimeField(
null=True,
verbose_name='',
help_text='',
)
DeRegistrationEffectiveDate = CopyFromDateTimeField(
null=True,
verbose_name='',
help_text='',
)
AnniversaryDate = CopyFromDateTimeField(
verbose_name='',
help_text='',
)
CBIFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
CBIUnsanitizedVersionFlag = CopyFromBooleanField(
verbose_name='',
help_text='',
)
VersionNumber = CopyFromCharField(
max_length=200,
blank=True,
verbose_name='',
help_text='',
)
FRS_Lat = CopyFromDecimalField(
null=True,
max_digits=17,
decimal_places=15,
verbose_name='',
help_text='',
)
FRS_Long = CopyFromDecimalField(
null=True,
max_digits=17,
decimal_places=14,
verbose_name='',
help_text='',
)
FRS_Description = CopyFromCharField(
max_length=40,
blank=True,
verbose_name='',
help_text='',
)
FRS_Method = CopyFromCharField(
max_length=60,
blank=True,
verbose_name='',
help_text='',
)
HorizontalAccMeasure = CopyFromCharField(
max_length=6,
blank=True,
verbose_name='',
help_text='',
)
HorizontalRefDatumCode = CopyFromCharField(
max_length=3,
blank=True,
)
SourceMapScaleNumber = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='',
help_text='',
)
EmergencyContactEmail = CopyFromEmailField(
max_length=100,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerName = CopyFromCharField(
max_length=70,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerStreet1 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerStreet2 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerCity = CopyFromCharField(
max_length=30,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerState = CopyFromForeignKey(
'tlkpStateFIPSCodes',
db_column='RMPPreparerState',
on_delete=models.PROTECT,
related_name='rmppreparerstate',
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerZIP = CopyFromCharField(
max_length=5,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerZIP4Ext = CopyFromCharField(
max_length=4,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerTelephone = CopyFromCharField(
max_length=10,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerForeignStateOrProvince = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerForeignCountry = CopyFromForeignKey(
'tlkpForeignCountry',
db_column='RMPPreparerForeignCountry',
on_delete=models.PROTECT,
related_name='rmppreparerforeigncountry',
blank=True,
verbose_name='',
help_text='',
)
RMPPreparerForeignPostalCode = CopyFromCharField(
max_length=14,
blank=True,
verbose_name='',
help_text='',
)
RMPSubmissionReasonCode = CopyFromForeignKey(
'tlkpSubmissionReasonCodes',
on_delete=models.PROTECT,
source_column='RMPSubmissionReasonCode',
blank=True,
verbose_name='',
help_text='',
)
RMPEmail = CopyFromEmailField(
max_length=100,
blank=True,
verbose_name='',
help_text='',
)
DeregistrationReasonCode = CopyFromForeignKey(
'tlkpDeregistrationReason',
db_column='DeregistrationReasonCode',
on_delete=models.PROTECT,
blank=True,
verbose_name='',
help_text='',
)
DeregistrationReasonOtherText = CopyFromCharField(
max_length=80,
blank=True,
verbose_name='',
help_text='',
)
source_file = 'tblS1Facilities'
class Meta:
verbose_name = 'Registration Information'
verbose_name_plural = 'Registration Information'
class Facility(BaseRMPModel):
id = CopyFromBigIntegerField(primary_key=True, )
facility_name = CopyFromCharField(max_length=50, )
rmp = CopyFromForeignKey(
'Registration',
on_delete=models.PROTECT,
)
street_1 = CopyFromCharField(max_length=35, )
street_2 = CopyFromCharField(max_length=35, )
city = CopyFromCharField(max_length=19, )
state = CopyFromForeignKey(
'StateCd',
on_delete=models.PROTECT,
db_column='state',
)
zip_code = CopyFromCharField(max_length=5, )
zip_ext = CopyFromCharField(max_length=4)
county_fips = CopyFromForeignKey(
'CountyCd',
on_delete=models.PROTECT,
db_column='county_fips',
null=True,
)
county_name = CopyFromCharField(max_length=50, null=True)
num_registrations = CopyFromIntegerField()
latitude = CopyFromDecimalField(
max_digits=6,
decimal_places=3,
)
longitude = CopyFromDecimalField(
max_digits=6,
decimal_places=3,
)
sub_type = CopyFromCharField(
max_length=1,
blank=True,
choices=choices.SUBMISSION_TYPES,
)
sub_date = CopyFromDateField()
execsum_rmp = CopyFromForeignKey(
'ExecutiveSummary',
on_delete=models.PROTECT,
)
exec_sub_type = CopyFromCharField(max_length=1, blank=True)
exec_sub_date = CopyFromDateTimeField()
# these fields could be converted to DateTime once we replace "0000-00-00" with NULL
deregistration_date = CopyFromCharField(
max_length=10,
blank=True,
null=True,
)
dereg_effect_date = CopyFromCharField(
max_length=10,
blank=True,
null=True,
)
parent = CopyFromCharField(max_length=200, blank=True)
parent_2 = CopyFromCharField(max_length=200, blank=True)
operator_name = CopyFromCharField(max_length=200, blank=True)
operator_city = CopyFromCharField(max_length=20, blank=True)
operator_state = CopyFromForeignKey(
'StateCd',
on_delete=models.PROTECT,
blank=True,
null=True,
db_column='operator_state',
related_name='+',
)
operator_zip = CopyFromCharField(max_length=5, blank=True)
province = CopyFromCharField(max_length=20, blank=True)
county = CopyFromCharField(max_length=200, blank=True)
country = CopyFromCharField(max_length=25, blank=True)
sub_reason = CopyFromForeignKey(
'SubmitCd',
on_delete=models.PROTECT,
blank=True,
null=True,
db_column='sub_reason',
)
dereg_reason = CopyFromForeignKey(
'DeregCd',
on_delete=models.PROTECT,
blank=True,
null=True,
db_column='dereg_reason',
)
dereg_other = CopyFromCharField(max_length=255, blank=True)
registered = CopyFromBooleanField(default=True)
num_fte = CopyFromIntegerField(null=True)
num_accident_actual = CopyFromIntegerField(null=True)
num_accident_records = CopyFromIntegerField(null=True)
num_accident_divider = CopyFromIntegerField(null=True)
acc_flam_tot = CopyFromIntegerField(null=True)
acc_toxic_tot = CopyFromIntegerField(null=True)
acc_quantity_tot = CopyFromIntegerField(null=True)
num_deaths = CopyFromIntegerField()
num_injuries = CopyFromIntegerField()
num_evacuated = CopyFromIntegerField()
property_damage = CopyFromIntegerField()
@classmethod
def get_transform_queryset(self):
"""
Facility takes all of the raw data up to the num_registrations field. After that, the aggregated fields come from the facility's most recent
registration (the field is called sub_date in the processed model and ReceiptDate in the raw model).
Due to the nature of the foreign key relationships, I had to do the same calculations I did on Regisration as I did on Facility, calculating a
divider using the count of accidents on the accident table.
Again, problem fields on this model include all of the aggregated chemical quantity fields (anything with the _tot flag).
"""
qs = raw_models.tblFacility.objects.filter(
tbls1facilities__FacilityID=Subquery(
raw_models.tblS1Facilities.objects.filter(
EPAFacilityID=OuterRef('EPAFacilityID'), ).values(
'FacilityID').annotate(max_sub_date=Max('FacilityID')).
values('max_sub_date').order_by('-max_sub_date')[:1])
).select_related('FacilityCountyFIPS', ).annotate(
id=F('EPAFacilityID'),
facility_name=F('FacilityName'),
rmp_id=F('FacilityID'),
street_1=F('FacilityStr1'),
street_2=F('FacilityStr2'),
city=F('FacilityCity'),
state=F('FacilityState'),
zip_code=F('FacilityZipCode'),
zip_ext=F('Facility4DigitZipExt'),
county_fips=F('FacilityCountyFIPS'),
county_name=F('FacilityCountyFIPS__County_Name'),
num_registrations=F('CountOfFacilityID'),
latitude=F('tbls1facilities__FacilityLatDecDegs'),
longitude=F('tbls1facilities__FacilityLongDecDegs'),
sub_type=F('tbls1facilities__SubmissionType'),
sub_date=F('tbls1facilities__ReceiptDate'),
execsum_rmp_id=F('FacilityID'),
exec_sub_type=F('tbls1facilities__SubmissionType'),
exec_sub_date=F('tbls1facilities__ReceiptDate'),
deregistration_date=F('tbls1facilities__DeRegistrationDate'),
dereg_effect_date=F(
'tbls1facilities__DeRegistrationEffectiveDate'),
parent=F('tbls1facilities__ParentCompanyName'),
parent_2=F('tbls1facilities__Company2Name'),
operator_name=F('tbls1facilities__OperatorName'),
operator_city=F('tbls1facilities__OperatorCity'),
operator_state=F('tbls1facilities__OperatorStateFIPS'),
operator_zip=F('tbls1facilities__OperatorZipCode'),
province=F('tbls1facilities__ForeignStateProv'),
county=F('tbls1facilities__FacilityCountyFIPS'),
country=F('tbls1facilities__ForeignCountry'),
sub_reason=F('tbls1facilities__RMPSubmissionReasonCode'),
dereg_reason=F('tbls1facilities__DeregistrationReasonCode'),
dereg_other=F('tbls1facilities__DeregistrationReasonOtherText'),
registered=Case(When(dereg_reason__gt=0, then=0),
default=Value(1),
output_field=CopyFromBooleanField()),
num_fte=F('tbls1facilities__FTE'),
all_naics=F(
'tbls1facilities__tbls1processes__tbls1process_naics__NAICSCode'
),
num_accident_records=Count(
'tbls1facilities__tbls6accidenthistory', ),
num_accident_actual=Count(
'tbls1facilities__tbls6accidenthistory',
distinct=True,
),
num_accident_divider=Case(When(num_accident_actual=0, then=1),
default=F('num_accident_records') /
F('num_accident_actual')),
acc_flam_tot=Round(
Sum(
Case(When(
tbls1facilities__tbls6accidenthistory__tbls6accidentchemicals__ChemicalID__ChemType
='F',
then=
('tbls1facilities__tbls6accidenthistory__tbls6accidentchemicals__QuantityReleased'
)),
default=Value(0),
output_field=CopyFromIntegerField())) /
F('num_accident_divider'), ),
acc_toxic_tot=Round(
Sum(
Case(When(
tbls1facilities__tbls6accidenthistory__tbls6accidentchemicals__ChemicalID__ChemType
='T',
then=
('tbls1facilities__tbls6accidenthistory__tbls6accidentchemicals__QuantityReleased'
)),
default=Value(0),
output_field=CopyFromIntegerField())) /
F('num_accident_divider'), ),
acc_quantity_tot=F('acc_flam_tot') + F('acc_toxic_tot'),
num_deaths=Coalesce(
Round(
Sum(F(
'tbls1facilities__tbls6accidenthistory__DeathsWorkers')
+
F('tbls1facilities__tbls6accidenthistory__DeathsPublicResponders'
) +
F('tbls1facilities__tbls6accidenthistory__DeathsPublic'
),
default=Value(0),
output_field=CopyFromIntegerField()) /
F('num_accident_divider'), ),
Value(0),
),
num_injuries=Coalesce(
Round(
Sum(F(
'tbls1facilities__tbls6accidenthistory__InjuriesPublic'
) + F(
'tbls1facilities__tbls6accidenthistory__InjuriesWorkers'
) + F(
'tbls1facilities__tbls6accidenthistory__InjuriesPublicResponders'
),
default=Value(0),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
Value(0),
),
num_evacuated=Coalesce(
Round(
Sum(F('tbls1facilities__tbls6accidenthistory__Evacuated'),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
Value(0),
),
property_damage=Coalesce(
Round(
Sum(F(
'tbls1facilities__tbls6accidenthistory__OnsitePropertyDamage'
) + F(
'tbls1facilities__tbls6accidenthistory__OffsitePropertyDamage'
),
output_field=CopyFromIntegerField()) /
F('num_accident_divider')),
Value(0),
),
)
return qs
@property
def google_maps_url(self):
url = 'https://www.google.com/maps/search/?api=1&query={},{}'.format(
self.latitude,
self.longitude,
)
return url
@property
def has_parent_1(self):
return self.parent_1 != ''
@property
def has_parent_2(self):
return self.parent_2 != ''
class Meta:
indexes = [
models.Index(fields=['registered', '-num_deaths']),
models.Index(fields=['registered', '-num_evacuated']),
models.Index(fields=['registered', '-property_damage']),
]
class Accident(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='accident_id',
)
rmp = CopyFromForeignKey(
'Registration',
on_delete=models.PROTECT,
)
# accident_date = CopyFromDateField(
# max_length=19,
# null=True,
# blank=True,
# )
# ^^^^^^^ This is the proper config for this field, once we
# clean up "null" vs "NULL" values
accident_date = CopyFromCharField(blank=True, max_length=10)
# can we combine these two? do we know the time zone?
# accident_date = CopyFromDateTimeField(blank=True, null=True)
accident_time = CopyFromCharField(max_length=4)
naics_code = CopyFromCharField(
source_column='naics',
max_length=6,
)
release_duration = CopyFromCharField(max_length=5)
re_gas = CopyFromBooleanField()
re_spill = CopyFromBooleanField()
re_fire = CopyFromBooleanField()
re_explosion = CopyFromBooleanField()
re_reactive_incident = CopyFromBooleanField(
source_column='re_reactiveincid',
)
rs_storage_vessel = CopyFromBooleanField(
source_column='rs_storagevessel',
)
rs_piping = CopyFromBooleanField()
rs_process_vessel = CopyFromBooleanField(
source_column='rs_processvessel',
)
rs_transfer_hose = CopyFromBooleanField(
source_column='rs_transferhose',
)
rs_valve = CopyFromBooleanField()
rs_pump = CopyFromBooleanField()
rs_joint = CopyFromBooleanField()
other_release_source = CopyFromCharField(
source_column='rs_other',
max_length=200,
blank=True
)
wind_speed = CopyFromDecimalField(
null=True,
max_digits=6,
decimal_places=2,
)
wind_speed_unit = CopyFromCharField(max_length=1, blank=True)
wind_direction = CopyFromCharField(max_length=3, blank=True)
temperature = CopyFromIntegerField(
# Data type of source column is decimal, but all distinct values
# are whole numbers NONETHELESS, should prob convert to decimal field
# in case the source data includes decimal values
# means outputtin as such in processed file
null=True,
)
stability_class = CopyFromCharField(max_length=1, blank=True)
precipitation = CopyFromBooleanField(
source_column='precipitation_yn',
)
unknown_weather = CopyFromBooleanField()
deaths_workers = CopyFromIntegerField()
deaths_responders = CopyFromIntegerField()
deaths_public = CopyFromIntegerField()
injuries_workers = CopyFromIntegerField()
injuries_responders = CopyFromIntegerField(
source_column='injuries_respond',
)
injuries_public = CopyFromIntegerField()
onsite_damage = CopyFromIntegerField()
offsite_deaths = CopyFromBooleanField()
hospitalization = CopyFromIntegerField(
source_column='offsite_hosp',
)
offsite_medical = CopyFromIntegerField()
offsite_evacuated = CopyFromIntegerField()
offsite_shelter = CopyFromIntegerField()
offsite_damage = CopyFromIntegerField()
ed_kills = CopyFromBooleanField()
ed_defoliation = CopyFromBooleanField()
ed_water_contamination = CopyFromBooleanField(
source_column='ed_watercontam',
)
ed_soil_contamination = CopyFromBooleanField(
source_column='ed_soilcontam',
)
ed_other = CopyFromCharField(
max_length=200,
blank=True
)
initiating_event = CopyFromCharField(
max_length=1,
blank=True
)
cf_equipment_failure = CopyFromBooleanField(
source_column='cf_equipmentfail',
)
cf_human_error = CopyFromBooleanField(
source_column='cf_humanerror',
)
cf_improper_procedure = CopyFromBooleanField(
source_column='cf_impprocedure',
)
cf_overpressure = CopyFromBooleanField()
cf_upset_condition = CopyFromBooleanField(
source_column='cf_upsetcondition'
)
cf_bypass_condition = CopyFromBooleanField(
source_column='cf_bypasscond',
)
cf_maintenance = CopyFromBooleanField()
cf_process_design_failure = CopyFromBooleanField(
source_column='cf_processdesign',
)
cf_unsuitable_equipment = CopyFromBooleanField(
source_column='cf_unsuitequip',
)
cf_unusual_weather = CopyFromBooleanField(
source_column='cf_unusualweather',
)
cf_management_error = CopyFromBooleanField(
source_column='cf_management',
)
cf_other = CopyFromCharField(
max_length=200,
blank=True,
)
offsite_responders_notify = CopyFromCharField(
max_length=25,
blank=True,
source_column='offsite_notify',
)
ci_improved_equipment = CopyFromBooleanField(
source_column='ci_improveequip',
)
ci_revised_maintenance = CopyFromBooleanField(
source_column='ci_revisedmaint',
)
ci_revised_training = CopyFromBooleanField(
source_column='ci_revisedtrain',
)
ci_revised_op_procedures = CopyFromBooleanField(
source_column='ci_opprocedures',
)
ci_new_process_controls = CopyFromBooleanField(
source_column='ci_processcontrol',
)
ci_new_mitigation_systems = CopyFromBooleanField(
source_column='ci_mitigationsys',
)
ci_response_plan = CopyFromBooleanField(
source_column='ci_responseplan',
)
ci_changed_process = CopyFromBooleanField(
source_column='ci_changedprocess',
)
ci_reduced_inventory = CopyFromBooleanField(
source_column='ci_reducedinv',
)
ci_none = CopyFromBooleanField()
ci_other = CopyFromCharField(max_length=200, blank=True)
cbi_flag = CopyFromBooleanField()
#TODO TURN BELOW INTO AGGREGATE
num_acc_chem = CopyFromIntegerField()
flam_total = CopyFromIntegerField(
source_column='flam_tot'
)
toxic_total = CopyFromIntegerField(
source_column='toxic_tot'
)
quantity_total = CopyFromIntegerField(
source_column='quantity_tot'
)
num_deaths = CopyFromIntegerField()
num_injuries = CopyFromIntegerField()
num_evacuated = CopyFromIntegerField()
property_damage = CopyFromIntegerField()
env_damage = CopyFromIntegerField()
# TODO SEE IF THIS WORKS
def save(self, *args, **kwargs):
self.quantity_tot = self.flam_tot + self.toxic_tot
super(Accident, self).save(*args, **kwargs)
class tblFacility(BaseRMPModel):
EPAFacilityID = CopyFromBigIntegerField(
verbose_name='EPA Facility Identifier',
primary_key=True,
help_text='Unique identifier for all RMPs submitted by a specific '
'facility (assigned by Reporting Center for first-time '
'submission). * After this number is assigned to the first '
'submission for a facility, subsequent submissions for the '
'same facility must include this identifier.')
FacilityName = CopyFromCharField(
verbose_name='Facility Name',
max_length=200,
help_text='Facility name specific to the site.',
)
MarplotID = CopyFromCharField(
max_length=100,
blank=True,
verbose_name='Marplot Identifier',
help_text='Future link to Marplot',
)
CameoID = CopyFromCharField(
max_length=100,
blank=True,
verbose_name='Cameo Identifier',
help_text='Future Link to CAMEO',
)
FacilityID = CopyFromOneToOneField(
'tblS1Facilities',
source_column='RMPID',
db_column='FacilityID',
on_delete=models.PROTECT,
)
FacilityStr1 = CopyFromCharField(
max_length=35,
blank=True,
)
FacilityStr2 = CopyFromCharField(
max_length=35,
blank=True,
)
FacilityCity = CopyFromCharField(max_length=19, )
FacilityState = CopyFromForeignKey(
'tlkpStateFIPSCodes',
db_column='FacilityState',
on_delete=models.PROTECT,
max_length=2,
)
FacilityZipCode = CopyFromCharField(max_length=5, )
Facility4DigitZipExt = CopyFromCharField(
max_length=4,
blank=True,
)
FacilityCountyFIPS = CopyFromForeignKey(
'TlkpCountyFIPSCodes',
db_column='FacilityCountyFIPS',
on_delete=models.PROTECT,
null=True,
)
CountOfFacilityID = CopyFromIntegerField()
FacilityLatDecDegs = CopyFromDecimalField(
decimal_places=15,
max_digits=18,
null=True,
)
FacilityLongDecDegs = CopyFromDecimalField(
decimal_places=15,
max_digits=18,
null=True,
)
class Meta:
verbose_name = 'RMP Facility Information'
verbose_name_plural = 'RMP Facility Information'
class Registration(BaseRMPModel):
rmp_id = CopyFromIntegerField(
primary_key=True
)
facility_name = CopyFromCharField(max_length=255, blank=True)
street_1 = CopyFromCharField(max_length=35, blank=True)
street_2 = CopyFromCharField(max_length=35, blank=True)
city = CopyFromCharField(max_length=19, blank=True)
state = CopyFromCharField(max_length=2, blank=True)
zip = CopyFromCharField(max_length=5, blank=True)
zip_ext = CopyFromCharField(max_length=4, blank=True)
county_fips = CopyFromCharField(max_length=5, blank=True)
lepc = CopyFromCharField(max_length=30, blank=True)
latitude_dec = CopyFromCharField(max_length=10, blank=True)
longitude_dec = CopyFromCharField(max_length=11, blank=True)
valid_latlong = CopyFromCharField(max_length=1, blank=True)
latlong_meth = CopyFromCharField(max_length=2, blank=True)
latlong_desc = CopyFromCharField(max_length=2, blank=True)
facility_url = CopyFromCharField(max_length=100, blank=True)
facility_phone = CopyFromCharField(max_length=10, blank=True)
facility_email = CopyFromCharField(max_length=100, blank=True)
facility_duns = CopyFromCharField(max_length=9, blank=True)
facility_email = CopyFromCharField(max_length=100, blank=True)
parent = CopyFromCharField(max_length=250, blank=True)
parent_2 = CopyFromCharField(max_length=50, blank=True)
parent_duns = CopyFromCharField(max_length=9, blank=True)
parent2_duns = CopyFromCharField(max_length=9, blank=True)
operator_name = CopyFromCharField(max_length=250, blank=True)
operator_phone = CopyFromCharField(max_length=10, blank=True)
op_street_1 = CopyFromCharField(max_length=35, blank=True)
op_street_2 = CopyFromCharField(max_length=35, blank=True)
operator_city = CopyFromCharField(max_length=19, blank=True)
operator_state = CopyFromCharField(max_length=2, blank=True)
operator_zip = CopyFromCharField(max_length=5, blank=True)
operator_zip_ext = CopyFromCharField(max_length=4, blank=True)
rmp_contact = CopyFromCharField(max_length=35, blank=True)
rmp_contact_title = CopyFromCharField(max_length=250, blank=True)
em_contact_name = CopyFromCharField(max_length=250, blank=True)
em_contact_title = CopyFromCharField(max_length=35, blank=True)
em_contact_phone = CopyFromCharField(max_length=10, blank=True)
phone_24hour = CopyFromCharField(max_length=10, blank=True)
phone_24hour_ext = CopyFromCharField(max_length=10, blank=True)
num_fte = CopyFromIntegerField(null=True)
other_facility_id = CopyFromCharField(blank=True, max_length=15)
facility_id = CopyFromBigIntegerField(
# 'Facility',
# on_delete=models.CASCADE,
)
osha_psm_yn = CopyFromBooleanField()
epcra_302_yn = CopyFromBooleanField()
caa_title_v_yn = CopyFromBooleanField()
caa_permit_id = CopyFromCharField(blank=True, max_length=15)
# safety_inspect_dt = CopyFromDateTimeField(
# blank=True,
# null=True
# )
safety_inspect_dt = CopyFromCharField(blank=True, max_length=10)
safety_inspect_by = CopyFromCharField(max_length=50, blank=True)
osha_ranking = CopyFromBooleanField()
predictive_file_yn = CopyFromBooleanField()
submission_type = CopyFromCharField(max_length=1, blank=True)
rmp_desc = CopyFromCharField(max_length=50, blank=True)
no_accidents_yn = CopyFromBooleanField()
foreign_province = CopyFromCharField(max_length=35, blank=True)
foreign_zip = CopyFromCharField(max_length=14, blank=True)
foreign_country = CopyFromCharField(max_length=2, blank=True)
num_fte_cbi_flag = CopyFromBooleanField()
# complete_check_dt = CopyFromDateTimeField(blank=True, null=True)
complete_check_dt = CopyFromCharField(blank=True, max_length=10)
# error_report_dt = CopyFromDateTimeField(blank=True, null=True)
error_report_dt = CopyFromCharField(blank=True, max_length=10)
receipt_date = CopyFromCharField(max_length=25, blank=True)
graphics_ind = CopyFromBooleanField()
attachment_ind = CopyFromBooleanField()
cert_rec_flag = CopyFromBooleanField()
submit_method = CopyFromCharField(max_length=50, blank=True)
cbi_substant_flag = CopyFromBooleanField()
elect_waiver_flag = CopyFromBooleanField()
postmark_date = CopyFromCharField(max_length=25, blank=True)
rmp_complete_flag = CopyFromCharField(max_length=1, blank=True)
# deregistration_dt = CopyFromDateTimeField(blank=True, null=True)
deregistration_dt = CopyFromCharField(blank=True, max_length=10)
# dereg_effect_dt = CopyFromDateTimeField(blank=True, null=True)
dereg_effect_dt = CopyFromCharField(blank=True, max_length=10)
# anniversary_date = CopyFromDateTimeField(blank=True, null=True)
anniversary_date = CopyFromCharField(blank=True, max_length=10)
cbi_flag = CopyFromBooleanField()
unsanitized_vers = CopyFromBooleanField()
version_number = CopyFromCharField(max_length=15, blank=True)
frs_lat_dec = CopyFromDecimalField(
max_digits=5, decimal_places=2, null=True
)
frs_long_dec = CopyFromDecimalField(
max_digits=5, decimal_places=2, null=True
)
frs_ll_desc = CopyFromCharField(max_length=40, blank=True)
frs_ll_method = CopyFromCharField(max_length=60, blank=True)
hor_measure = CopyFromCharField(max_length=6, blank=True)
hor_ref = CopyFromCharField(max_length=3, blank=True)
source_scale = CopyFromCharField(max_length=10, blank=True)
em_email = CopyFromCharField(max_length=100, blank=True)
prep_name = CopyFromCharField(max_length=70, blank=True)
prep_street_1 = CopyFromCharField(max_length=35, blank=True)
prep_street_2 = CopyFromCharField(max_length=35, blank=True)
prep_city = CopyFromCharField(max_length=19, blank=True)
prep_state = CopyFromCharField(max_length=2, blank=True)
prep_zip = CopyFromCharField(max_length=5, blank=True)
prep_zip_ext = CopyFromCharField(max_length=4, blank=True)
prep_phone = CopyFromCharField(max_length=10, blank=True)
prep_foreign_state = CopyFromCharField(max_length=35, blank=True)
prep_country = CopyFromCharField(max_length=2, blank=True)
prep_foreign_zip = CopyFromCharField(max_length=14, blank=True)
sub_reason = CopyFromCharField(max_length=3, blank=True)
rmp_email = CopyFromCharField(max_length=100, blank=True)
dereg_reason = CopyFromCharField(max_length=2, blank=True)
dereg_other = CopyFromCharField(max_length=80, blank=True)
# TODO AGGREGATE
num_accident = CopyFromIntegerField()
num_facility = CopyFromIntegerField()
num_process = CopyFromIntegerField()
num_response = CopyFromIntegerField()
num_chem_real = CopyFromIntegerField()
num_worst_tox = CopyFromIntegerField()
num_alt_tox = CopyFromIntegerField()
num_worst_flam = CopyFromIntegerField()
num_alt_flam = CopyFromIntegerField()
num_prev_2 = CopyFromIntegerField()
num_prev_3 = CopyFromIntegerField()
toxic_tot = CopyFromBigIntegerField()
flam_tot = CopyFromBigIntegerField()
acc_flam_tot = CopyFromIntegerField()
acc_toxic_tot = CopyFromIntegerField()
acc_quantity_tot = CopyFromIntegerField()
num_deaths = CopyFromIntegerField()
num_injuries = CopyFromIntegerField()
num_evacuated = CopyFromIntegerField()
property_damage = CopyFromIntegerField()
county = CopyFromCharField(max_length=60, blank=True)
foreign_country_tr = CopyFromCharField(max_length=60, blank=True)
num_proc_23 = CopyFromBigIntegerField()
toxic_tot_23 = CopyFromBigIntegerField()
flam_tot_23 = CopyFromBigIntegerField()
quantity_tot_23 = CopyFromBigIntegerField()
num_execsum_mod = CopyFromIntegerField()
execsum_type = CopyFromCharField(max_length=1, blank=True)
num_execsum = CopyFromIntegerField()
num_exec_sum = CopyFromIntegerField()
