class tblS9EmergencyResponses(BaseRMPModel):
FacilityID = CopyFromOneToOneField(
'tblS1Facilities',
db_column='FacilityID',
on_delete=models.PROTECT,
)
ER_CommunityPlan = CopyFromBooleanField()
ER_FacilityPlan = CopyFromBooleanField()
ER_ResponseActions = CopyFromBooleanField()
ER_PublicInfoProcedures = CopyFromBooleanField()
ER_EmergencyHealthCare = CopyFromBooleanField()
ER_ReviewDate = CopyFromDateTimeField(null=True, )
ERTrainingDate = CopyFromDateTimeField(null=True, )
CoordinatingAgencyName = CopyFromCharField(
max_length=250,
blank=True,
)
CoordinatingAgencyPhone = CopyFromCharField(
source_column='CoordinatingAgencyPhone',
max_length=10,
blank=True,
)
FR_OSHA1910_38 = CopyFromBooleanField()
FR_OSHA1910_120 = CopyFromBooleanField()
FR_SPCC = CopyFromBooleanField()
FR_RCRA = CopyFromBooleanField()
FR_OPA90 = CopyFromBooleanField()
FR_EPCRA = CopyFromBooleanField()
FR_OtherRegulation = CopyFromCharField(
max_length=200,
blank=True,
)
class Meta:
verbose_name = 'Emergency Response Plan'
verbose_name_plural = 'Emergency Response Plans'
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 tblS8PreventionProgram2(BaseRMPModel):
preventionprogram2id = CopyFromIntegerField(
source_column='PreventionProgram2ID',
primary_key=True,
)
process_naics_id = CopyFromForeignKey(
'tblS1ProcessNAICS',
source_column='Process_NAICS_ID',
on_delete=models.PROTECT,
)
safetyreviewdate = CopyFromDateTimeField(
source_column='SafetyReviewDate',
null=True,
)
fr_nfpa58 = CopyFromBooleanField(source_column='FR_NFPA58', )
fr_osha = CopyFromBooleanField(source_column='FR_OSHA', )
fr_astm = CopyFromBooleanField(source_column='FR_ASTM', )
fr_ansi = CopyFromBooleanField(source_column='FR_ANSI', )
fr_asme = CopyFromBooleanField(source_column='FR_ASME', )
fr_none = CopyFromBooleanField(source_column='FR_None', )
fr_othertype = CopyFromCharField(
source_column='FR_OtherType',
max_length=200,
blank=True,
)
fr_comments = CopyFromCharField(
source_column='FR_Comments',
max_length=200,
blank=True,
)
hazardreviewdate = CopyFromDateTimeField(
source_column='HazardReviewDate',
null=True,
)
changecompletiondate = CopyFromDateTimeField(
source_column='ChangeCompletionDate',
null=True,
)
mh_toxicrelease = CopyFromBooleanField(source_column='MH_ToxicRelease', )
mh_fire = CopyFromBooleanField(source_column='MH_Fire', )
mh_explosion = CopyFromBooleanField(source_column='MH_Explosion', )
mh_runawayreaction = CopyFromBooleanField(
source_column='MH_RunawayReaction', )
mh_polymerization = CopyFromBooleanField(
source_column='MH_Polymerization', )
mh_overpressurization = CopyFromBooleanField(
source_column='MH_Overpressurization', )
mh_corrosion = CopyFromBooleanField(source_column='MH_Corrosion', )
mh_overfilling = CopyFromBooleanField(source_column='MH_Overfilling', )
mh_contamination = CopyFromBooleanField(source_column='MH_Contamination', )
mh_equipmentfailure = CopyFromBooleanField(
source_column='MH_EquipmentFailure', )
mh_coolingloss = CopyFromBooleanField(source_column='MH_CoolingLoss', )
mh_earthquake = CopyFromBooleanField(source_column='MH_Earthquake', )
mh_floods = CopyFromBooleanField(source_column='MH_Floods', )
mh_tornado = CopyFromBooleanField(source_column='MH_Tornado', )
mh_hurricanes = CopyFromBooleanField(source_column='MH_Hurricanes', )
mh_othertype = CopyFromCharField(
source_column='MH_OtherType',
max_length=200,
blank=True,
)
pc_vents = CopyFromBooleanField(source_column='PC_Vents', )
pc_reliefvalves = CopyFromBooleanField(source_column='PC_ReliefValves', )
pc_checkvalves = CopyFromBooleanField(source_column='PC_CheckValves', )
pc_scrubbers = CopyFromBooleanField(source_column='PC_Scrubbers', )
pc_flares = CopyFromBooleanField(source_column='PC_Flares', )
pc_manualshutoffs = CopyFromBooleanField(
source_column='PC_ManualShutoffs', )
pc_automaticshutoffs = CopyFromBooleanField(
source_column='PC_AutomaticShutoffs', )
pc_interlocks = CopyFromBooleanField(source_column='PC_Interlocks', )
pc_alarms = CopyFromBooleanField(source_column='PC_Alarms', )
pc_keyedbypass = CopyFromBooleanField(source_column='PC_KeyedBypass', )
pc_emergencyairsupply = CopyFromBooleanField(
source_column='PC_EmergencyAirSupply', )
pc_emergencypower = CopyFromBooleanField(
source_column='PC_EmergencyPower', )
pc_backuppump = CopyFromBooleanField(source_column='PC_BackupPump', )
pc_groundingequipment = CopyFromBooleanField(
source_column='PC_GroundingEquipment', )
pc_inhibitoraddition = CopyFromBooleanField(
source_column='PC_InhibitorAddition', )
pc_rupturedisks = CopyFromBooleanField(source_column='PC_RuptureDisks', )
pc_excessflowdevice = CopyFromBooleanField(
source_column='PC_ExcessFlowDevice', )
pc_quenchsystem = CopyFromBooleanField(source_column='PC_QuenchSystem', )
pc_purgesystem = CopyFromBooleanField(source_column='PC_PurgeSystem', )
pc_none = CopyFromBooleanField(source_column='PC_None', )
pc_othertype = CopyFromCharField(
source_column='PC_OtherType',
max_length=200,
blank=True,
)
ms_sprinklersystem = CopyFromBooleanField(
source_column='MS_SprinklerSystem', )
ms_dikes = CopyFromBooleanField(source_column='MS_Dikes', )
ms_firewalls = CopyFromBooleanField(source_column='MS_FireWalls', )
ms_blastwalls = CopyFromBooleanField(source_column='MS_BlastWalls', )
ms_delugesystem = CopyFromBooleanField(source_column='MS_DelugeSystem', )
ms_watercurtain = CopyFromBooleanField(source_column='MS_WaterCurtain', )
ms_enclosure = CopyFromBooleanField(source_column='MS_Enclosure', )
ms_neutralization = CopyFromBooleanField(
source_column='MS_Neutralization', )
ms_none = CopyFromBooleanField(source_column='MS_None', )
ms_othertype = CopyFromCharField(
source_column='MS_OtherType',
max_length=200,
blank=True,
)
md_processareadetectors = CopyFromBooleanField(
source_column='MD_ProcessAreaDetectors', )
md_perimetermonitors = CopyFromBooleanField(
source_column='MD_PerimeterMonitors', )
md_none = CopyFromBooleanField(source_column='MD_None', )
md_othertype = CopyFromCharField(
source_column='MD_OtherType',
max_length=200,
blank=True,
)
ch_chemicalreduction = CopyFromBooleanField(
source_column='CH_ChemicalReduction', )
ch_chemicalincrease = CopyFromBooleanField(
source_column='CH_ChemicalIncrease', )
ch_changeprocessparameters = CopyFromBooleanField(
source_column='CH_ChangeProcessParameters', )
ch_installprocesscontrols = CopyFromBooleanField(
source_column='CH_InstallProcessControls', )
ch_installprocessdetection = CopyFromBooleanField(
source_column='CH_InstallProcessDetection', )
ch_installperimetermonitoring = CopyFromBooleanField(
source_column='CH_InstallPerimeterMonitoring', )
ch_installmitigationsystems = CopyFromBooleanField(
source_column='CH_InstallMitigationSystems', )
ch_nonerequired = CopyFromBooleanField(source_column='CH_NoneRequired', )
ch_none = CopyFromBooleanField(source_column='CH_None', )
ch_otherchanges = CopyFromCharField(
source_column='CH_OtherChanges',
max_length=200,
blank=True,
)
opproceduresreviewdate = CopyFromDateTimeField(
source_column='OpProceduresReviewDate',
null=True,
)
trainingreviewdate = CopyFromDateTimeField(
source_column='TrainingReviewDate',
null=True,
)
tr_classroom = CopyFromBooleanField(source_column='TR_Classroom', )
tr_onthejob = CopyFromBooleanField(source_column='TR_OnTheJob', )
tr_othertype = CopyFromCharField(
source_column='TR_OtherType',
max_length=200,
blank=True,
)
ct_writtentest = CopyFromBooleanField(source_column='CT_WrittenTest', )
ct_oraltest = CopyFromBooleanField(source_column='CT_OralTest', )
ct_demonstration = CopyFromBooleanField(source_column='CT_Demonstration', )
ct_observation = CopyFromBooleanField(source_column='CT_Observation', )
ct_othertype = CopyFromCharField(
source_column='CT_OtherType',
max_length=200,
blank=True,
)
maintenancereviewdate = CopyFromDateTimeField(
source_column='MaintenanceReviewDate',
null=True,
)
equipmentinspectiondate = CopyFromDateTimeField(
source_column='EquipmentInspectionDate',
null=True,
)
equipmenttested = CopyFromCharField(
source_column='EquipmentTested',
max_length=200,
blank=True,
)
complianceauditdate = CopyFromDateTimeField(
source_column='ComplianceAuditDate',
null=True,
)
auditcompletiondate = CopyFromDateTimeField(
source_column='AuditCompletionDate',
null=True,
)
incidentinvestigationdate = CopyFromDateTimeField(
source_column='IncidentInvestigationDate',
null=True,
)
investigationchangedate = CopyFromDateTimeField(
source_column='InvestigationChangeDate',
null=True,
)
mostrecentchangedate = CopyFromDateTimeField(
source_column='MostRecentChangeDate',
null=True,
)
cbi_flag = CopyFromBooleanField(source_column='CBI_Flag', )
description = CopyFromTextField(
source_column='Description',
blank=True,
)
class Meta:
verbose_name = 'Prevention Program: Program Level 2'
verbose_name_plural = 'Prevention Program: Program Level 2'
class tblS7PreventionProgram3(BaseRMPModel):
PreventionProgram3ID = CopyFromIntegerField(
primary_key=True,
)
Process_NAICS_ID = CopyFromForeignKey(
'tblS1Process_NAICS',
db_column='Process_NAICS_ID',
on_delete=models.PROTECT,
)
SafetyReviewDate = CopyFromDateTimeField(
null=True,
)
PHA_Date = CopyFromDateTimeField(
null=True,
)
PHA_WhatIf = CopyFromBooleanField(
)
PHA_Checklist = CopyFromBooleanField(
)
PHA_WhatIfChecklist = CopyFromBooleanField(
)
PHA_HAZOP = CopyFromBooleanField(
)
PHA_FMEA = CopyFromBooleanField(
)
PHA_FTA = CopyFromBooleanField(
)
PHA_OtherTechnique = CopyFromCharField(
max_length=200,
blank=True,
)
PHACompletionDate = CopyFromDateTimeField(
null=True,
)
MH_ToxicRelease = CopyFromBooleanField(
)
MH_Fire = CopyFromBooleanField(
)
MH_Explosion = CopyFromBooleanField(
)
MH_RunawayReaction = CopyFromBooleanField(
)
MH_Polymerization = CopyFromBooleanField(
)
MH_Overpressurization = CopyFromBooleanField(
)
MH_Corrosion = CopyFromBooleanField(
)
MH_Overfilling = CopyFromBooleanField(
)
MH_Contamination = CopyFromBooleanField(
)
MH_EquipmentFailure = CopyFromBooleanField(
)
MH_CoolingLoss = CopyFromBooleanField(
)
MH_Earthquake = CopyFromBooleanField(
)
MH_Floods = CopyFromBooleanField(
)
MH_Tornado = CopyFromBooleanField(
)
MH_Hurricanes = CopyFromBooleanField(
)
MH_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
PC_Vents = CopyFromBooleanField(
)
PC_ReliefValves = CopyFromBooleanField(
)
PC_CheckValves = CopyFromBooleanField(
)
PC_Scrubbers = CopyFromBooleanField(
)
PC_Flares = CopyFromBooleanField(
)
PC_ManualShutoffs = CopyFromBooleanField(
)
PC_AutomaticShutoffs = CopyFromBooleanField(
)
PC_Interlocks = CopyFromBooleanField(
)
PC_Alarms = CopyFromBooleanField(
)
PC_KeyedBypass = CopyFromBooleanField(
)
PC_EmergencyAirSupply = CopyFromBooleanField(
)
PC_EmergencyPower = CopyFromBooleanField(
)
PC_BackupPump = CopyFromBooleanField(
)
PC_GroundingEquipment = CopyFromBooleanField(
)
PC_InhibitorAddition = CopyFromBooleanField(
)
PC_RuptureDisks = CopyFromBooleanField(
)
PC_ExcessFlowDevice = CopyFromBooleanField(
)
PC_QuenchSystem = CopyFromBooleanField(
)
PC_PurgeSystem = CopyFromBooleanField(
)
PC_None = CopyFromBooleanField(
)
PC_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
MS_SprinklerSystem = CopyFromBooleanField(
)
MS_Dikes = CopyFromBooleanField(
)
MS_FireWalls = CopyFromBooleanField(
)
MS_BlastWalls = CopyFromBooleanField(
)
MS_DelugeSystem = CopyFromBooleanField(
)
MS_WaterCurtain = CopyFromBooleanField(
)
MS_Enclosure = CopyFromBooleanField(
)
MS_Neutralization = CopyFromBooleanField(
)
MS_None = CopyFromBooleanField(
)
MS_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
MD_ProcessAreaDetectors = CopyFromBooleanField(
)
MD_PerimeterMonitors = CopyFromBooleanField(
)
MD_None = CopyFromBooleanField(
)
MD_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
CH_ChemicalReduction = CopyFromBooleanField(
)
CH_ChemicalIncrease = CopyFromBooleanField(
)
CH_ChangeProcessParameters = CopyFromBooleanField(
)
CH_InstallProcessControls = CopyFromBooleanField(
)
CH_InstallProcessDetection = CopyFromBooleanField(
)
CH_InstallPerimeterMonitoring = CopyFromBooleanField(
)
CH_InstallMitigationSystems = CopyFromBooleanField(
)
CH_NoneRequired = CopyFromBooleanField(
)
CH_None = CopyFromBooleanField(
)
CH_OtherChanges = CopyFromCharField(
max_length=200,
blank=True,
)
OpProceduresReviewDate = CopyFromDateTimeField(
null=True,
)
TrainingReviewDate = CopyFromDateTimeField(
null=True,
)
TR_Classroom = CopyFromBooleanField(
)
TR_OnTheJob = CopyFromBooleanField(
)
TR_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
CT_WrittenTest = CopyFromBooleanField(
)
CT_OralTest = CopyFromBooleanField(
)
CT_Demonstration = CopyFromBooleanField(
)
CT_Observation = CopyFromBooleanField(
)
CT_OtherType = CopyFromCharField(
max_length=200, blank=True
)
MaintenanceReviewDate = CopyFromDateTimeField(
null=True,
)
EquipmentInspectionDate = CopyFromDateTimeField(
null=True,
)
EquipmentTested = CopyFromCharField(
max_length=200,
blank=True,
)
ChangeMgmtDate = CopyFromDateTimeField(
null=True,
)
ChangeMgmtReviewDate = CopyFromDateTimeField(
null=True,
)
PreStartupReviewDate = CopyFromDateTimeField(
null=True,
)
ComplianceAuditDate = CopyFromDateTimeField(
null=True,
)
AuditCompletionDate = CopyFromDateTimeField(
null=True,
)
IncidentInvestigationDate = CopyFromDateTimeField(
null=True,
)
InvestigationChangeDate = CopyFromDateTimeField(
null=True,
)
ParticipationPlansReviewDate = CopyFromDateTimeField(
null=True,
)
HotWorkPermitReviewDate = CopyFromDateTimeField(
null=True,
)
ContractorSafetyReviewDate = CopyFromDateTimeField(
null=True,
)
ContractorSafetyEvalDate = CopyFromDateTimeField(
null=True,
)
CBI_Flag = CopyFromBooleanField(
)
Description = CopyFromTextField(
blank=True,
) # TODO to rmp_prev3text with prevention programID
class Meta:
verbose_name = 'Prevention Program: Program Level 3'
verbose_name_plural = 'Prevention Programs: Program Level 3'
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 tblS6AccidentHistory(BaseRMPModel):
AccidentHistoryID = CopyFromIntegerField(
primary_key=True,
)
FacilityID = CopyFromForeignKey(
'Tbls1Facilities',
db_column='FacilityID',
on_delete=models.PROTECT,
)
AccidentDate = CopyFromDateTimeField(
null=True,
)
AccidentTime = CopyFromCharField(
max_length=4,
blank=True,
)
NAICSCode = CopyFromForeignKey(
'tlkpNAICS',
db_column='NAICSCode',
on_delete=models.PROTECT,
blank=True,
)
AccidentReleaseDuration = CopyFromCharField(
max_length=5,
blank=True,
)
RE_Gas = CopyFromBooleanField(
)
RE_Spill = CopyFromBooleanField(
)
RE_Fire = CopyFromBooleanField(
)
RE_Explosion = CopyFromBooleanField(
)
RE_ReactiveIncident = CopyFromBooleanField(
)
RS_StorageVessel = CopyFromBooleanField(
)
RS_Piping = CopyFromBooleanField(
)
RS_ProcessVessel = CopyFromBooleanField(
)
RS_TransferHose = CopyFromBooleanField(
)
RS_Valve = CopyFromBooleanField(
)
RS_Pump = CopyFromBooleanField(
)
RS_Joint = CopyFromBooleanField(
)
OtherReleaseSource = CopyFromCharField(
max_length=200,
blank=True,
)
WindSpeed = CopyFromFloatField(
null=True,
)
WindSpeedUnitCode = CopyFromForeignKey(
'tlkpWindSpeedUnitCodes',
db_column='WindSpeedUnitCode',
on_delete=models.PROTECT,
blank=True,
)
WindDirection = CopyFromCharField(
max_length=3,
blank=True,
)
Temperature = CopyFromFloatField(
null=True,
)
StabilityClass = CopyFromCharField(
max_length=1,
blank=True,
)
Precipitation = CopyFromBooleanField(
)
WeatherUnknown = CopyFromBooleanField(
)
DeathsWorkers = CopyFromIntegerField(
null=True,
)
DeathsPublicResponders = CopyFromIntegerField(
null=True,
)
DeathsPublic = CopyFromIntegerField(
null=True,
)
InjuriesWorkers = CopyFromIntegerField(
null=True,
)
InjuriesPublicResponders = CopyFromIntegerField(
null=True,
)
InjuriesPublic = CopyFromIntegerField(
null=True,
)
OnsitePropertyDamage = CopyFromFloatField(
null=True,
)
OffsiteDeaths = CopyFromBooleanField(
null=True,
)
Hospitalization = CopyFromFloatField(
null=True,
)
MedicalTreatment = CopyFromFloatField(
null=True,
)
Evacuated = CopyFromFloatField(
null=True,
)
ShelteredInPlace = CopyFromFloatField(
null=True,
)
OffsitePropertyDamage = CopyFromFloatField(
null=True,
)
ED_Kills = CopyFromBooleanField(
)
ED_MinorDefoliation = CopyFromBooleanField(
)
ED_WaterContamination = CopyFromBooleanField(
)
ED_SoilContamination = CopyFromBooleanField(
)
ED_Other = CopyFromCharField(
max_length=200,
blank=True,
)
InitiatingEvent = CopyFromForeignKey(
'tlkpS6InitiatingEvents',
on_delete=models.PROTECT,
blank=True,
)
CF_EquipmentFailure = CopyFromBooleanField(
)
CF_HumanError = CopyFromBooleanField(
)
CF_ImproperProcedure = CopyFromBooleanField(
)
CF_Overpressurization = CopyFromBooleanField(
)
CF_UpsetCondition = CopyFromBooleanField(
)
CF_BypassCondition = CopyFromBooleanField(
)
CF_Maintenance = CopyFromBooleanField(
)
CF_ProcessDesignFailure = CopyFromBooleanField(
)
CF_UnsuitableEquipment = CopyFromBooleanField(
)
CF_UnusualWeather = CopyFromBooleanField(
)
CF_ManagementError = CopyFromBooleanField(
)
CF_Other = CopyFromCharField(
max_length=200,
blank=True,
)
OffsiteRespondersNotify = CopyFromCharField(
max_length=25,
blank=True,
)
CI_ImprovedEquipment = CopyFromBooleanField(
)
CI_RevisedMaintenance = CopyFromBooleanField(
)
CI_RevisedTraining = CopyFromBooleanField(
)
CI_RevisedOpProcedures = CopyFromBooleanField(
)
CI_NewProcessControls = CopyFromBooleanField(
)
CI_NewMitigationSystems = CopyFromBooleanField(
)
CI_RevisedERPlan = CopyFromBooleanField(
)
CI_ChangedProcess = CopyFromBooleanField(
)
CI_ReducedInventory = CopyFromBooleanField(
)
CI_None = CopyFromBooleanField(
)
CI_OtherType = CopyFromCharField(
max_length=200,
blank=True,
)
CBI_Flag = CopyFromBooleanField(
)
class Meta:
verbose_name = 'Accident History'
verbose_name_plural = 'Accident History'
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 tblS6AccidentHistory(BaseRMPModel):
accidenthistoryid = CopyFromIntegerField(
source_column='AccidentHistoryID',
primary_key=True,
)
facilityid = CopyFromForeignKey(
'Tbls1Facilities',
source_column='FacilityID',
on_delete=models.PROTECT,
)
accidentdate = CopyFromDateTimeField(
source_column='AccidentDate',
null=True,
)
accidenttime = CopyFromCharField(
source_column='AccidentTime',
max_length=4,
blank=True,
)
naicscode = CopyFromForeignKey(
'tlkpNAICS',
source_column='NAICSCode',
on_delete=models.PROTECT,
blank=True,
)
accidentreleaseduration = CopyFromCharField(
source_column='AccidentReleaseDuration',
max_length=5,
blank=True,
)
re_gas = CopyFromBooleanField(source_column='RE_Gas', )
re_spill = CopyFromBooleanField(source_column='RE_Spill', )
re_fire = CopyFromBooleanField(source_column='RE_Fire', )
re_explosion = CopyFromBooleanField(source_column='RE_Explosion', )
re_reactiveincident = CopyFromBooleanField(
source_column='RE_ReactiveIncident', )
rs_storagevessel = CopyFromBooleanField(source_column='RS_StorageVessel', )
rs_piping = CopyFromBooleanField(source_column='RS_Piping', )
rs_processvessel = CopyFromBooleanField(source_column='RS_ProcessVessel', )
rs_transferhose = CopyFromBooleanField(source_column='RS_TransferHose', )
rs_valve = CopyFromBooleanField(source_column='RS_Valve', )
rs_pump = CopyFromBooleanField(source_column='RS_Pump', )
rs_joint = CopyFromBooleanField(source_column='RS_Joint', )
otherreleasesource = CopyFromCharField(
source_column='OtherReleaseSource',
max_length=200,
blank=True,
)
windspeed = CopyFromFloatField(
source_column='WindSpeed',
null=True,
)
windspeedunitcode = CopyFromForeignKey(
'tlkpWindSpeedUnitCodes',
source_column='WindSpeedUnitCode',
on_delete=models.PROTECT,
blank=True,
)
winddirection = CopyFromCharField(
source_column='WindDirection',
max_length=3,
blank=True,
)
temperature = CopyFromFloatField(
source_column='Temperature',
null=True,
)
stabilityclass = CopyFromCharField(
source_column='StabilityClass',
max_length=1,
blank=True,
)
precipitation = CopyFromBooleanField(source_column='Precipitation', )
weatherunknown = CopyFromBooleanField(source_column='WeatherUnknown', )
deathsworkers = CopyFromIntegerField(
source_column='DeathsWorkers',
null=True,
)
deathspublicresponders = CopyFromIntegerField(
source_column='DeathsPublicResponders',
null=True,
)
deathspublic = CopyFromIntegerField(
source_column='DeathsPublic',
null=True,
)
injuriesworkers = CopyFromIntegerField(
source_column='InjuriesWorkers',
null=True,
)
injuriespublicresponders = CopyFromIntegerField(
source_column='InjuriesPublicResponders',
null=True,
)
injuriespublic = CopyFromIntegerField(
source_column='InjuriesPublic',
null=True,
)
onsitepropertydamage = CopyFromFloatField(
source_column='OnsitePropertyDamage',
null=True,
)
offsitedeaths = CopyFromBooleanField(
source_column='OffsiteDeaths',
null=True,
)
hospitalization = CopyFromFloatField(
source_column='Hospitalization',
null=True,
)
medicaltreatment = CopyFromFloatField(
source_column='MedicalTreatment',
null=True,
)
evacuated = CopyFromFloatField(
source_column='Evacuated',
null=True,
)
shelteredinplace = CopyFromFloatField(
source_column='ShelteredInPlace',
null=True,
)
offsitepropertydamage = CopyFromFloatField(
source_column='OffsitePropertyDamage',
null=True,
)
ed_kills = CopyFromBooleanField(source_column='ED_Kills', )
ed_minordefoliation = CopyFromBooleanField(
source_column='ED_MinorDefoliation', )
ed_watercontamination = CopyFromBooleanField(
source_column='ED_WaterContamination', )
ed_soilcontamination = CopyFromBooleanField(
source_column='ED_SoilContamination', )
ed_other = CopyFromCharField(
source_column='ED_Other',
max_length=200,
blank=True,
)
initiatingevent = CopyFromForeignKey(
'tlkpS6InitiatingEvents',
source_column='InitiatingEvent',
on_delete=models.PROTECT,
blank=True,
)
cf_equipmentfailure = CopyFromBooleanField(
source_column='CF_EquipmentFailure', )
cf_humanerror = CopyFromBooleanField(source_column='CF_HumanError', )
cf_improperprocedure = CopyFromBooleanField(
source_column='CF_ImproperProcedure', )
cf_overpressurization = CopyFromBooleanField(
source_column='CF_Overpressurization', )
cf_upsetcondition = CopyFromBooleanField(
source_column='CF_UpsetCondition', )
cf_bypasscondition = CopyFromBooleanField(
source_column='CF_BypassCondition', )
cf_maintenance = CopyFromBooleanField(source_column='CF_Maintenance', )
cf_processdesignfailure = CopyFromBooleanField(
source_column='CF_ProcessDesignFailure', )
cf_unsuitableequipment = CopyFromBooleanField(
source_column='CF_UnsuitableEquipment', )
cf_unusualweather = CopyFromBooleanField(
source_column='CF_UnusualWeather', )
cf_managementerror = CopyFromBooleanField(
source_column='CF_ManagementError', )
cf_other = CopyFromCharField(
source_column='CF_Other',
max_length=200,
blank=True,
)
offsiterespondersnotify = CopyFromCharField(
source_column='OffsiteRespondersNotify',
max_length=25,
blank=True,
)
ci_improvedequipment = CopyFromBooleanField(
source_column='CI_ImprovedEquipment', )
ci_revisedmaintenance = CopyFromBooleanField(
source_column='CI_RevisedMaintenance', )
ci_revisedtraining = CopyFromBooleanField(
source_column='CI_RevisedTraining', )
ci_revisedopprocedures = CopyFromBooleanField(
source_column='CI_RevisedOpProcedures', )
ci_newprocesscontrols = CopyFromBooleanField(
source_column='CI_NewProcessControls', )
ci_newmitigationsystems = CopyFromBooleanField(
source_column='CI_NewMitigationSystems', )
ci_revisederplan = CopyFromBooleanField(source_column='CI_RevisedERPlan', )
ci_changedprocess = CopyFromBooleanField(
source_column='CI_ChangedProcess', )
ci_reducedinventory = CopyFromBooleanField(
source_column='CI_ReducedInventory', )
ci_none = CopyFromBooleanField(source_column='CI_None', )
ci_othertype = CopyFromCharField(
source_column='CI_OtherType',
max_length=200,
blank=True,
)
cbi_flag = CopyFromBooleanField(source_column='CBI_Flag', )
class Meta:
verbose_name = 'Accident History'
verbose_name_plural = 'Accident History'
class tblS9EmergencyResponses(BaseRMPModel):
facilityid = CopyFromOneToOneField(
'tblS1Facilities',
source_column='FacilityID',
on_delete=models.PROTECT,
)
er_communityplan = CopyFromBooleanField(
source_column='ER_CommunityPlan',
)
er_facilityplan = CopyFromBooleanField(
source_column='ER_FacilityPlan',
)
er_responseactions = CopyFromBooleanField(
source_column='ER_ResponseActions',
)
er_publicinfoprocedures = CopyFromBooleanField(
source_column='ER_PublicInfoProcedures',
)
er_emergencyhealthcare = CopyFromBooleanField(
source_column='ER_EmergencyHealthCare',
)
er_reviewdate = CopyFromDateTimeField(
source_column='ER_ReviewDate',
null=True,
)
ertrainingdate = CopyFromDateTimeField(
source_column='ERTrainingDate',
null=True,
)
coordinatingagencyname = CopyFromCharField(
source_column='CoordinatingAgencyName',
max_length=250,
blank=True,
)
coordinatingagencyphone = CopyFromCharField(
source_column='CoordinatingAgencyPhone',
max_length=10,
blank=True,
)
fr_osha1910_38 = CopyFromBooleanField(
source_column='FR_OSHA1910_38',
)
fr_osha1910_120 = CopyFromBooleanField(
source_column='FR_OSHA1910_120',
)
fr_spcc = CopyFromBooleanField(
source_column='FR_SPCC',
)
fr_rcra = CopyFromBooleanField(
source_column='FR_RCRA',
)
fr_opa90 = CopyFromBooleanField(
source_column='FR_OPA90',
)
fr_epcra = CopyFromBooleanField(
source_column='FR_EPCRA',
)
fr_otherregulation = CopyFromCharField(
source_column='FR_OtherRegulation',
max_length=200,
blank=True,
)
class Meta:
verbose_name = 'Emergency Response Plan'
verbose_name_plural = 'Emergency Response Plans'
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 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 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