class Process(BaseRMPModel):
id = CopyFromIntegerField(
primary_key=True,
source_column='process_id',
)
process_desc = CopyFromCharField(max_length=25, blank=True)
rmp_id = CopyFromIntegerField()
program_level = CopyFromIntegerField()
cbi_flag = CopyFromBooleanField()
# TODO AGGREGATE
num_proc_chem = CopyFromIntegerField()
num_proc_naics = CopyFromIntegerField()
num_chem_real = CopyFromIntegerField()
num_chem_fake = CopyFromIntegerField()
num_worst_tox = CopyFromIntegerField()
num_worst_flam = CopyFromIntegerField()
num_alt_tox = CopyFromIntegerField()
num_alt_flam = CopyFromIntegerField()
num_prev_2 = CopyFromIntegerField()
num_prev_3 = CopyFromIntegerField()
toxic_tot = CopyFromBigIntegerField()
flam_tot = CopyFromBigIntegerField()
quantity_tot = CopyFromBigIntegerField()
# while these fields are listed in rmp_fields.csv, they do not appear in
# rmp_process.tsv ¯\_(ツ)_/¯
# chem_tox_yn = CopyFromBooleanField()
# chem_flam_yn = CopyFromBooleanField()
source_file = 'rmp_process'
class ProcChem(BaseRMPModel):
procchem_id = CopyFromBigIntegerField(
primary_key=True,
source_column='procchem_id',
)
process = CopyFromForeignKey(
'Process',
on_delete=models.CASCADE
)
chemical = CopyFromForeignKey(
'ChemCd',
on_delete=models.CASCADE
)
quantity_lbs = CopyFromBigIntegerField()
cbi_flag = CopyFromBooleanField()
num_alt_flam = CopyFromBigIntegerField()
num_alt_tox = CopyFromBigIntegerField()
num_prevent_2_chem = CopyFromBigIntegerField()
num_prevent_3_chem = CopyFromBigIntegerField()
num_proc_flam = CopyFromBigIntegerField()
num_worst_flam = CopyFromBigIntegerField()
num_worst_tox = CopyFromBigIntegerField()
cas = CopyFromBigIntegerField()
chemical_type = CopyFromCharField(max_length=1)
source_file = 'rmp_proc_chem'
def get_transform_queryset(self):
"""
Top level table for Process. Aggregated fields are calculated similar to other tables. Fields with num_ are calculated by getting the count of
ProcessChemicalIDs from each table.
flam_tot and toxic_tot are calculated by generating the sum of process chemicals grouping by ProcessID.
"""
qs = raw_models.tblS1Processes.objects.select_related(
'FacilityID',
).annotate(
process_id=F('ProcessID'),
process_desc=F('AltID'),
rmp_id=F('FacilityID'),
program_level=F('ProgramLevel'),
cbi_flag=F('CBI_Flag'),
num_proc_chem=Count('tbls1processchemicals'),
num_proc_naics=Count('tbls1process_naics'),
num_worst_tox=Count(
'tbls1processchemicals__tbls2toxicsworstcase__ProcessChemicalID'
),
num_worst_flam=Count(
'tbls1processchemicals__tbls4flammablesworstcase'),
num_alt_tox=Count(
'tbls1processchemicals__tbls3toxicsaltreleases__ProcessChemicalID'
),
num_alt_flam=Count(
'tbls1processchemicals__tbls5flammablesaltreleases'),
flam_tot=Sum(
Case(When(tbls1processchemicals__ChemicalID__ChemType='F',
then=(Cast('tbls1processchemicals__Quantity',
CopyFromBigIntegerField()))),
default=Value(0))),
toxic_tot=Sum(
Case(When(tbls1processchemicals__ChemicalID__ChemType='T',
then=(Cast('tbls1processchemicals__Quantity',
CopyFromBigIntegerField()))),
default=Value(0))),
quantity_tot=F('flam_tot') + F('toxic_tot'),
facility_name=F('FacilityID__FacilityName'),
facility_id=F('FacilityID__EPAFacilityID'),
rmp_receipt_date=F('FacilityID__ReceiptDate'))
return qs
def get_transform_queryset(self):
"""
Process Chemicals contians 8 aggregated fields and a foreign key join to the tlkpChemicals table. All aggregated fields are getting counts of
ProcessChemicalID from alt_flam, alt_tox, preventionprogram2, preventionprogram3, process flam, worst case flammable and worst case toxic.
"""
qs = raw_models.tblS1ProcessChemicals.objects.select_related(
'ChemicalID', ).annotate(
procchem_id=F('ProcessChemicalID'),
process_id=F('ProcessID'),
chemical_id=F('ChemicalID'),
chemical_name=F('ChemicalID__ChemicalName'),
quantity_lbs=Cast('Quantity', CopyFromBigIntegerField()),
cbi_flag=F('CBI_Flag'),
num_alt_flam=Count('tbls5flammablesaltreleases'),
num_alt_tox=Count('tbls3toxicsaltreleases'),
num_prevent_2_chem=Count('tbls8_prevention_program_chemicals'),
num_prevent_3_chem=Count('tbls7_prevention_program_chemicals'),
num_proc_flam=Count('tbls1flammablemixturechemicals'),
num_worst_flam=Count('tbls4flammablesworstcase'),
num_worst_tox=Count('tbls2toxicsworstcase'),
cas=F('ChemicalID__CASNumber'),
chemical_type=F('ChemicalID__ChemType'),
worst_tox_flag=Case(
When(
ProcessChemicalID=F(
'tbls2toxicsworstcase__ProcessChemicalID'),
then=(Cast(True, CopyFromBooleanField())),
),
default=Cast(False, CopyFromBooleanField()),
),
worst_flam_flag=Case(
When(
ProcessChemicalID=F(
'tbls4flammablesworstcase__ProcessChemicalID'),
then=(Cast(True, CopyFromBooleanField())),
),
default=Cast(False, CopyFromBooleanField()),
),
)
return qs
class tblS2ToxicsWorstCase(BaseRMPModel):
toxicid = CopyFromIntegerField(
primary_key=True,
source_column='ToxicID',
)
processchemicalid = CopyFromForeignKey(
'tblS1ProcessChemicals',
on_delete=models.PROTECT,
source_column='ProcessChemicalID',
)
percentweight = CopyFromFloatField(
source_column='PercentWeight',
null=True,
)
physicalstate = CopyFromCharField(
source_column='PhysicalState',
max_length=1,
blank=True,
)
analyticalbasis = CopyFromCharField(
source_column='AnalyticalBasis',
max_length=255,
blank=True,
)
scenario = CopyFromBooleanField(
source_column='Scenario',
null=True,
)
quantityreleased = CopyFromFloatField(
source_column='QuantityReleased',
null=True,
)
releaseduration = CopyFromFloatField(
source_column='ReleaseDuration',
null=True,
)
releaserate = CopyFromFloatField(
source_column='ReleaseRate',
null=True,
)
windspeed = CopyFromFloatField(source_column='WindSpeed', )
stabilityclass = CopyFromCharField(
source_column='StabilityClass',
max_length=1,
blank=True,
)
topography = CopyFromForeignKey(
'tlkpTopographyCode',
on_delete=models.PROTECT,
source_column='Topography',
blank=True,
)
endpoint_distance = CopyFromFloatField(
source_column='Distance2Endpoint',
null=True,
)
residentialpopulation = CopyFromBigIntegerField(
source_column='ResidentialPopulation',
null=True,
)
pr_schools = CopyFromBooleanField(source_column='PR_Schools', )
pr_residences = CopyFromBooleanField(source_column='PR_Residences', )
pr_hospitals = CopyFromBooleanField(source_column='PR_Hospitals', )
pr_prisons = CopyFromBooleanField(source_column='PR_Prisons', )
pr_publicrecreation = CopyFromBooleanField(
source_column='PR_PublicRecreation', )
pr_comm_ind = CopyFromBooleanField(source_column='PR_Comm_Ind', )
pr_othertype = CopyFromCharField(
source_column='PR_OtherType',
max_length=200,
blank=True,
)
er_natlstateparks = CopyFromBooleanField(
source_column='ER_NatlStateParks', )
er_wildlifesactuary = CopyFromBooleanField(
source_column='ER_WildlifeSactuary', )
er_fedwilderness = CopyFromBooleanField(source_column='ER_FedWilderness', )
er_othertype = CopyFromCharField(
source_column='ER_OtherType',
max_length=200,
blank=True,
)
pm_dikes = CopyFromBooleanField(source_column='PM_Dikes', )
pm_enclosures = CopyFromBooleanField(source_column='PM_Enclosures', )
pm_berms = CopyFromBooleanField(source_column='PM_Berms', )
pm_drains = CopyFromBooleanField(source_column='PM_Drains', )
pm_sumps = CopyFromBooleanField(source_column='PM_Sumps', )
pm_othertype = CopyFromCharField(
source_column='PM_OtherType',
max_length=200,
blank=True,
)
ptrgraphic = CopyFromBooleanField(
source_column='ptrGraphic',
max_length=12,
null=True,
)
cbi_flag = CopyFromBooleanField(source_column='CBI_Flag', )
class Meta:
verbose_name = 'Toxics: Worst Case Scenario'
verbose_name_plural = 'Toxics: Worst Case Scenarios'
class Process(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='process_id',
)
process_desc = CopyFromCharField(max_length=25, blank=True)
rmp = CopyFromForeignKey(
'Registration',
on_delete=models.PROTECT,
)
program_level = CopyFromIntegerField()
cbi_flag = CopyFromBooleanField()
num_proc_chem = CopyFromIntegerField()
num_proc_naics = CopyFromIntegerField()
num_worst_tox = CopyFromIntegerField()
num_worst_flam = CopyFromIntegerField()
num_alt_tox = CopyFromIntegerField()
num_alt_flam = CopyFromIntegerField()
# num_prev_2 = CopyFromIntegerField()
# num_prev_3 = CopyFromIntegerField()
toxic_tot = CopyFromBigIntegerField()
flam_tot = CopyFromBigIntegerField()
quantity_tot = CopyFromBigIntegerField()
facility_name = CopyFromCharField(
max_length=255,
blank=True,
)
facility_id = CopyFromBigIntegerField()
rmp_receipt_date = CopyFromDateTimeField(blank=True, )
@classmethod
def get_transform_queryset(self):
"""
Top level table for Process. Aggregated fields are calculated similar to other tables. Fields with num_ are calculated by getting the count of
ProcessChemicalIDs from each table.
flam_tot and toxic_tot are calculated by generating the sum of process chemicals grouping by ProcessID.
"""
qs = raw_models.tblS1Processes.objects.select_related(
'FacilityID',
).annotate(
process_id=F('ProcessID'),
process_desc=F('AltID'),
rmp_id=F('FacilityID'),
program_level=F('ProgramLevel'),
cbi_flag=F('CBI_Flag'),
num_proc_chem=Count('tbls1processchemicals'),
num_proc_naics=Count('tbls1process_naics'),
num_worst_tox=Count(
'tbls1processchemicals__tbls2toxicsworstcase__ProcessChemicalID'
),
num_worst_flam=Count(
'tbls1processchemicals__tbls4flammablesworstcase'),
num_alt_tox=Count(
'tbls1processchemicals__tbls3toxicsaltreleases__ProcessChemicalID'
),
num_alt_flam=Count(
'tbls1processchemicals__tbls5flammablesaltreleases'),
flam_tot=Sum(
Case(When(tbls1processchemicals__ChemicalID__ChemType='F',
then=(Cast('tbls1processchemicals__Quantity',
CopyFromBigIntegerField()))),
default=Value(0))),
toxic_tot=Sum(
Case(When(tbls1processchemicals__ChemicalID__ChemType='T',
then=(Cast('tbls1processchemicals__Quantity',
CopyFromBigIntegerField()))),
default=Value(0))),
quantity_tot=F('flam_tot') + F('toxic_tot'),
facility_name=F('FacilityID__FacilityName'),
facility_id=F('FacilityID__EPAFacilityID'),
rmp_receipt_date=F('FacilityID__ReceiptDate'))
return qs
def __str__(self):
if self.process_desc != '':
s = self.process_desc
else:
s = 'Unspecified process'
return s
class ProcChem(BaseRMPModel):
procchem_id = CopyFromBigIntegerField(
primary_key=True,
source_column='procchem_id',
)
process = CopyFromForeignKey(
'Process',
on_delete=models.PROTECT,
)
chemical = CopyFromForeignKey(
'ChemCd',
on_delete=models.PROTECT,
)
quantity_lbs = CopyFromBigIntegerField(
null=True,
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()
num_alt_flam = CopyFromBigIntegerField()
num_alt_tox = CopyFromBigIntegerField()
num_prevent_2_chem = CopyFromBigIntegerField()
num_prevent_3_chem = CopyFromBigIntegerField()
num_proc_flam = CopyFromBigIntegerField()
num_worst_flam = CopyFromBigIntegerField()
num_worst_tox = CopyFromBigIntegerField()
cas = CopyFromCharField(
max_length=9,
verbose_name='CAS number',
help_text='The identifying CAS number for a chemical.',
)
chemical_type = CopyFromCharField(max_length=1, blank=True)
chemical_name = CopyFromCharField(max_length=92)
worst_tox_flag = CopyFromBooleanField()
worst_flam_flag = CopyFromBooleanField()
@classmethod
def get_transform_queryset(self):
"""
Process Chemicals contians 8 aggregated fields and a foreign key join to the tlkpChemicals table. All aggregated fields are getting counts of
ProcessChemicalID from alt_flam, alt_tox, preventionprogram2, preventionprogram3, process flam, worst case flammable and worst case toxic.
"""
qs = raw_models.tblS1ProcessChemicals.objects.select_related(
'ChemicalID', ).annotate(
procchem_id=F('ProcessChemicalID'),
process_id=F('ProcessID'),
chemical_id=F('ChemicalID'),
chemical_name=F('ChemicalID__ChemicalName'),
quantity_lbs=Cast('Quantity', CopyFromBigIntegerField()),
cbi_flag=F('CBI_Flag'),
num_alt_flam=Count('tbls5flammablesaltreleases'),
num_alt_tox=Count('tbls3toxicsaltreleases'),
num_prevent_2_chem=Count('tbls8_prevention_program_chemicals'),
num_prevent_3_chem=Count('tbls7_prevention_program_chemicals'),
num_proc_flam=Count('tbls1flammablemixturechemicals'),
num_worst_flam=Count('tbls4flammablesworstcase'),
num_worst_tox=Count('tbls2toxicsworstcase'),
cas=F('ChemicalID__CASNumber'),
chemical_type=F('ChemicalID__ChemType'),
worst_tox_flag=Case(
When(
ProcessChemicalID=F(
'tbls2toxicsworstcase__ProcessChemicalID'),
then=(Cast(True, CopyFromBooleanField())),
),
default=Cast(False, CopyFromBooleanField()),
),
worst_flam_flag=Case(
When(
ProcessChemicalID=F(
'tbls4flammablesworstcase__ProcessChemicalID'),
then=(Cast(True, CopyFromBooleanField())),
),
default=Cast(False, CopyFromBooleanField()),
),
)
return qs
class tblS3ToxicsAltReleases(BaseRMPModel):
ToxicID = CopyFromIntegerField(primary_key=True, )
ProcessChemicalID = CopyFromForeignKey(
'tblS1ProcessChemicals',
on_delete=models.PROTECT,
db_column='ProcessChemicalID',
)
PercentWeight = CopyFromFloatField(null=True, )
PhysicalState = CopyFromForeignKey(
'tlkpPhysicalStateCodes',
on_delete=models.PROTECT,
blank=True,
db_column='PhysicalState',
)
AnalyticalBasis = CopyFromCharField(
max_length=255,
blank=True,
)
Scenario = CopyFromCharField(
max_length=200,
blank=True,
)
QuantityReleased = CopyFromFloatField(null=True, )
ReleaseDuration = CopyFromFloatField(null=True, )
ReleaseRate = CopyFromFloatField(null=True, )
WindSpeed = CopyFromFloatField(null=True, )
StabilityClass = CopyFromCharField(
max_length=1,
blank=True,
)
Topography = CopyFromForeignKey(
'tlkpTopographyCode',
on_delete=models.PROTECT,
blank=True,
db_column='Topography',
)
Distance2Endpoint = CopyFromFloatField(null=True, )
ResidentialPopulation = CopyFromBigIntegerField(null=True, )
PR_Schools = CopyFromBooleanField()
PR_Residences = CopyFromBooleanField()
PR_Hospitals = CopyFromBooleanField()
PR_Prisons = CopyFromBooleanField()
PR_PublicRecreation = CopyFromBooleanField()
PR_Comm_Ind = CopyFromBooleanField()
PR_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
ER_NatlStateParks = CopyFromBooleanField()
ER_WildlifeSactuary = 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,
)
AM_SprinklerSystems = CopyFromBooleanField()
AM_DelugeSystems = CopyFromBooleanField()
AM_WaterCurtain = CopyFromBooleanField()
AM_Neutralization = CopyFromBooleanField()
AM_ExcessFlowValve = CopyFromBooleanField()
AM_Flares = CopyFromBooleanField()
AM_Scrubbers = CopyFromBooleanField()
AM_EmergencyShutdown = CopyFromBooleanField()
AM_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
ptrGraphic = CopyFromCharField(
max_length=12,
blank=True,
)
CBI_Flag = CopyFromBooleanField()
class Meta:
verbose_name = 'Toxics: Alternative Release Scenario'
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 tblS5FlammablesAltReleases(BaseRMPModel):
flammableid = CopyFromIntegerField(
primary_key=True,
source_column='FlammableID',
)
processchemicalid = CopyFromForeignKey(
'tblS1ProcessChemicals',
on_delete=models.PROTECT,
source_column='ProcessChemicalID',
)
analyticalbasis = CopyFromCharField(
source_column='AnalyticalBasis',
max_length=255,
blank=True,
)
scenario = CopyFromCharField(
source_column='Scenario',
max_length=200,
null=True,
)
quantityreleased = CopyFromFloatField(
source_column='QuantityReleased',
null=True,
)
endpointused = CopyFromCharField(
source_column='EndpointUsed',
max_length=30,
blank=True,
)
lfl_value = CopyFromFloatField(
source_column='LFL_Value',
null=True,
)
distance2endpoint = CopyFromFloatField(
source_column='Distance2Endpoint',
null=True,
)
residentialpopulation = CopyFromBigIntegerField(
source_column='ResidentialPopulation',
null=True,
)
pr_schools = CopyFromBooleanField(source_column='PR_Schools', )
pr_residences = CopyFromBooleanField(source_column='PR_Residences', )
pr_hospitals = CopyFromBooleanField(source_column='PR_Hospitals', )
pr_prisons = CopyFromBooleanField(source_column='PR_Prisons', )
pr_publicrecreation = CopyFromBooleanField(
source_column='PR_PublicRecreation', )
pr_comm_ind = CopyFromBooleanField(source_column='PR_Comm_Ind', )
pr_othertype = CopyFromCharField(
source_column='PR_OtherType',
max_length=200,
blank=True,
)
er_natlstateparks = CopyFromBooleanField(
source_column='ER_NatlStateParks', )
er_wildlifesactuary = CopyFromBooleanField(
source_column='ER_WildlifeSactuary', )
er_fedwilderness = CopyFromBooleanField(source_column='ER_FedWilderness', )
er_othertype = CopyFromCharField(
source_column='ER_OtherType',
max_length=200,
blank=True,
)
pm_dikes = CopyFromBooleanField(source_column='PM_Dikes', )
pm_firewalls = CopyFromBooleanField(source_column='PM_FireWalls', )
pm_blastwalls = CopyFromBooleanField(source_column='PM_BlastWalls', )
pm_enclosures = CopyFromBooleanField(source_column='PM_Enclosures', )
pm_othertype = CopyFromCharField(
source_column='PM_OtherType',
max_length=200,
blank=True,
)
am_sprinklersystems = CopyFromBooleanField(
source_column='AM_SprinklerSystems', )
am_delugesystems = CopyFromBooleanField(source_column='AM_DelugeSystems', )
am_watercurtain = CopyFromBooleanField(source_column='AM_WaterCurtain', )
am_excessflowvalve = CopyFromBooleanField(
source_column='AM_ExcessFlowValve', )
am_othertype = CopyFromCharField(
source_column='AM_OtherType',
max_length=200,
blank=True,
)
ptrgraphic = CopyFromCharField(
source_column='ptrGraphic',
max_length=12,
blank=True,
)
cbi_flag = CopyFromBooleanField(source_column='CBI_Flag', )
class Meta:
verbose_name = 'Flammables: Alternative Release Scenario'
class tblS5FlammablesAltReleases(BaseRMPModel):
FlammableID = CopyFromIntegerField(primary_key=True, )
ProcessChemicalID = CopyFromForeignKey(
'tblS1ProcessChemicals',
db_column='ProcessChemicalID',
on_delete=models.PROTECT,
)
AnalyticalBasis = CopyFromCharField(
max_length=255,
blank=True,
)
Scenario = CopyFromCharField(
max_length=200,
blank=True,
)
QuantityReleased = CopyFromFloatField(null=True, )
EndpointUsed = CopyFromCharField(
max_length=30,
blank=True,
)
LFL_Value = CopyFromFloatField(null=True,
verbose_name='LFL Value (% Volume)')
Distance2Endpoint = CopyFromFloatField(null=True, )
ResidentialPopulation = CopyFromBigIntegerField(null=True, )
PR_Schools = CopyFromBooleanField()
PR_Residences = CopyFromBooleanField()
PR_Hospitals = CopyFromBooleanField()
PR_Prisons = CopyFromBooleanField()
PR_PublicRecreation = CopyFromBooleanField()
PR_Comm_Ind = CopyFromBooleanField()
PR_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
ER_NatlStateParks = CopyFromBooleanField()
ER_WildlifeSactuary = CopyFromBooleanField()
ER_FedWilderness = CopyFromBooleanField()
ER_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
PM_Dikes = CopyFromBooleanField()
PM_FireWalls = CopyFromBooleanField()
PM_BlastWalls = CopyFromBooleanField()
PM_Enclosures = CopyFromBooleanField()
PM_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
AM_SprinklerSystems = CopyFromBooleanField()
AM_DelugeSystems = CopyFromBooleanField()
AM_WaterCurtain = CopyFromBooleanField()
AM_ExcessFlowValve = CopyFromBooleanField()
AM_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
ptrGraphic = CopyFromCharField(
max_length=12,
blank=True,
)
CBI_Flag = CopyFromBooleanField()
class Meta:
verbose_name = 'Flammables: Alternative Release Scenario'
class tblS3ToxicsAltReleases(BaseRMPModel):
toxicid = CopyFromIntegerField(
source_column='ToxicID',
primary_key=True,
)
processchemicalid = CopyFromForeignKey(
'tblS1ProcessChemicals',
on_delete=models.PROTECT,
source_column='ProcessChemicalID',
)
percentweight = CopyFromFloatField(
source_column='PercentWeight',
null=True,
)
physicalstate = CopyFromForeignKey(
'tlkpPhysicalStateCodes',
on_delete=models.PROTECT,
source_column='PhysicalState',
blank=True,
)
analyticalbasis = CopyFromCharField(
source_column='AnalyticalBasis',
max_length=255,
blank=True,
)
scenario = CopyFromCharField(
source_column='Scenario',
max_length=200,
blank=True,
)
quantityreleased = CopyFromFloatField(
source_column='QuantityReleased',
null=True,
)
releaseduration = CopyFromFloatField(
source_column='ReleaseDuration',
null=True,
)
releaserate = CopyFromFloatField(
source_column='ReleaseRate',
null=True,
)
windspeed = CopyFromFloatField(
source_column='WindSpeed',
null=True,
)
stabilityclass = CopyFromCharField(
source_column='StabilityClass',
max_length=1,
blank=True,
)
topography = CopyFromForeignKey(
'tlkpTopographyCode',
on_delete=models.PROTECT,
source_column='Topography',
blank=True,
)
endpoint_distance = CopyFromFloatField(
source_column='Distance2Endpoint',
null=True,
)
residentialpopulation = CopyFromBigIntegerField(
source_column='ResidentialPopulation',
null=True,
)
pr_schools = CopyFromBooleanField(source_column='PR_Schools', )
pr_residences = CopyFromBooleanField(source_column='PR_Residences', )
pr_hospitals = CopyFromBooleanField(source_column='PR_Hospitals', )
pr_prisons = CopyFromBooleanField(source_column='PR_Prisons', )
pr_publicrecreation = CopyFromBooleanField(
source_column='PR_PublicRecreation', )
pr_comm_ind = CopyFromBooleanField(source_column='PR_Comm_Ind', )
pr_othertype = CopyFromCharField(
source_column='PR_OtherType',
max_length=200,
blank=True,
)
er_natlstateparks = CopyFromBooleanField(
source_column='ER_NatlStateParks', )
er_wildlifesactuary = CopyFromBooleanField(
source_column='ER_WildlifeSactuary', )
er_fedwilderness = CopyFromBooleanField(source_column='ER_FedWilderness', )
er_othertype = CopyFromCharField(
source_column='ER_OtherType',
max_length=200,
blank=True,
)
pm_dikes = CopyFromBooleanField(source_column='PM_Dikes', )
pm_enclosures = CopyFromBooleanField(source_column='PM_Enclosures', )
pm_berms = CopyFromBooleanField(source_column='PM_Berms', )
pm_drains = CopyFromBooleanField(source_column='PM_Drains', )
pm_sumps = CopyFromBooleanField(source_column='PM_Sumps', )
pm_othertype = CopyFromCharField(
source_column='PM_OtherType',
max_length=200,
blank=True,
)
am_sprinklersystems = CopyFromBooleanField(
source_column='AM_SprinklerSystems', )
am_delugesystems = CopyFromBooleanField(source_column='AM_DelugeSystems', )
am_watercurtain = CopyFromBooleanField(source_column='AM_WaterCurtain', )
am_neutralization = CopyFromBooleanField(
source_column='AM_Neutralization', )
am_excessflowvalve = CopyFromBooleanField(
source_column='AM_ExcessFlowValve', )
am_flares = CopyFromBooleanField(source_column='AM_Flares', )
am_scrubbers = CopyFromBooleanField(source_column='AM_Scrubbers', )
am_emergencyshutdown = CopyFromBooleanField(
source_column='AM_EmergencyShutdown', )
am_othertype = CopyFromCharField(
source_column='AM_OtherType',
max_length=200,
blank=True,
)
ptrgraphic = CopyFromCharField(
source_column='ptrGraphic',
max_length=12,
blank=True,
)
cbi_flag = CopyFromBooleanField(source_column='CBI_Flag', )
class Meta:
verbose_name = 'Toxics: Alternative Release Scenario'
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()
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 tblRMPTrack(BaseRMPModel):
TrackID = CopyFromBigIntegerField(
primary_key=True,
verbose_name='Track ID',
help_text='Unique number used to identify each RMP Tracking record.',
)
EPAFacilityID = CopyFromForeignKey(
'tblFacility',
db_column='EPAFacilityID',
on_delete=models.PROTECT,
null=True,
verbose_name='EPA Facility ID',
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.',
)
ReceiptDate = CopyFromDateTimeField(
verbose_name='Receipt Date',
help_text='The date the diskettes or paper submission was received by '
'the Records Center.',
)
PostmarkDate = CopyFromDateTimeField(
verbose_name='Postmark Date',
help_text='The postmark date of the RMP received material.',
)
FacilityName1 = CopyFromCharField(
max_length=50,
verbose_name='Facility Name 1',
help_text='The first line of the facility name.',
)
FacilityName2 = CopyFromCharField(
max_length=50,
blank=True,
verbose_name='Facility Name 2',
help_text='The second line of the facility name.',
)
FacilityStreet1 = CopyFromCharField(
max_length=35,
verbose_name='Facility Street 1',
help_text='The text which contains the first line of the mailing '
'address of the facility.',
)
FacilityStreet2 = CopyFromCharField(
max_length=35,
blank=True,
verbose_name='Facility Street 2',
help_text='The text which contains the second line of the mailing '
'address of the facility.',
)
FacilityCity = CopyFromCharField(
max_length=20,
verbose_name='Facility City',
help_text='The name of the city in which the facility is located.',
)
FacilityState = CopyFromForeignKey(
'tlkpStateFIPSCodes',
db_column='FacilityState',
on_delete=models.PROTECT,
verbose_name='Facility State',
help_text='The two-character FIPS abbreviation for the state in which '
'the facility is located.',
)
FacilityZip = CopyFromCharField(
max_length=5,
verbose_name='Facility Zip',
help_text='The code which represents the Zoning Improvement Plan (ZIP)'
' zone of the facility mailing address.',
)
FacilityZip4 = CopyFromCharField(
max_length=4,
blank=True,
verbose_name='Facility Zip4',
help_text='The code which represents the Zoning Improve Plan (ZIP) '
'Geographic Segment of the facility mailing address.',
)
DocumentHandle = CopyFromForeignKey(
'tlkpDocHandle',
db_column='DocumentHandle',
on_delete=models.PROTECT,
blank=True,
verbose_name='Document Handle',
help_text='The code which represents the method of handling used for '
'the RMP submission.',
)
Comments = CopyFromTextField(
verbose_name='Comments',
blank=True,
help_text='The text which contains comments about the RMP submission.',
)
DocumentType = CopyFromForeignKey(
'tlkpDocType',
db_column='DocumentType',
on_delete=models.PROTECT,
blank=True,
verbose_name='Document Type',
help_text='The code which represents the type of the document to which'
' the RMP Tracking record applies.',
)
RejectionReason = CopyFromForeignKey(
'tlkpRejectReason',
db_column='RejectionReason',
on_delete=models.PROTECT,
verbose_name='Rejection Reason',
help_text='The code which represents the reason the RMP submission '
'could not be processed.',
)
ResolutionDate = CopyFromDateTimeField(
null=True,
verbose_name='Resolution Date',
help_text='The date on which the problems which prevented the RMP from'
' being processed were finally resolved.',
)
InsertDate = CopyFromDateTimeField(null=True, )
class Meta:
verbose_name = 'RMP Tracking'
verbose_name_plural = 'RMP Tracking'
class tblS2ToxicsWorstCase(BaseRMPModel):
ToxicID = CopyFromIntegerField(primary_key=True, )
ProcessChemicalID = CopyFromForeignKey(
'tblS1ProcessChemicals',
on_delete=models.PROTECT,
)
PercentWeight = CopyFromFloatField(null=True, )
PhysicalState = CopyFromCharField(
source_column='PhysicalState',
max_length=1,
blank=True,
)
AnalyticalBasis = CopyFromCharField(
max_length=255,
blank=True,
)
Scenario = CopyFromBooleanField(null=True, )
QuantityReleased = CopyFromFloatField(null=True, )
ReleaseDuration = CopyFromFloatField(null=True, )
ReleaseRate = CopyFromFloatField(null=True, )
WindSpeed = CopyFromFloatField()
StabilityClass = CopyFromCharField(
max_length=1,
blank=True,
)
Topography = CopyFromForeignKey(
'tlkpTopographyCode',
db_column='Topography',
on_delete=models.PROTECT,
blank=True,
)
Distance2Endpoint = CopyFromFloatField(null=True, )
ResidentialPopulation = CopyFromBigIntegerField(null=True, )
PR_Schools = CopyFromBooleanField()
PR_Residences = CopyFromBooleanField()
PR_Hospitals = CopyFromBooleanField()
PR_Prisons = CopyFromBooleanField()
PR_PublicRecreation = CopyFromBooleanField()
PR_Comm_Ind = CopyFromBooleanField()
PR_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
ER_NatlStateParks = CopyFromBooleanField()
ER_WildlifeSactuary = 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 = CopyFromBooleanField(
max_length=12,
null=True,
)
CBI_Flag = CopyFromBooleanField()
class Meta:
verbose_name = 'Toxics: Worst Case Scenario'
verbose_name_plural = 'Toxics: Worst Case Scenarios'
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,
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