class SPIREGoodsIncident(BaseModel):
__tablename__ = 'goods_incidents'
id = _col(_int, primary_key=True, autoincrement=True)
inc_id = _col(_int, nullable=False)
type = _col(_text, nullable=False)
goods_item_id = _col(_int, nullable=False)
dest_country_id = _col(_int, nullable=False)
source_country_grp_id = _col(
_int, _foreign_key(f'{SPIRE_SCHEMA_NAME}.country_groups.id'))
report_date = _col(_dt, nullable=False)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, nullable=False)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
status_control = _col(_text, nullable=False)
ars = _relationship('SPIREArs', backref="goods_incident")
reasons_for_refusal = _relationship('SPIREReasonForRefusal',
backref='goods_incident')
control_entries = _relationship('SPIREControlEntry',
backref='goods_incident')
__table_args__ = (
_check("type IN ('REFUSAL', 'ISSUE', 'REVOKE', 'SURRENDER')"),
_check("version_no >= 0"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIRECountryGroup(BaseModel):
__tablename__ = 'country_groups'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
id = _col(_int, primary_key=True, autoincrement=True)
country_group_entries = _relationship('SPIRECountryGroupEntry',
backref='country_group')
goods_incidents = _relationship('SPIREGoodsIncident',
backref='country_group')
class SPIREMediaFootnote(BaseModel):
__tablename__ = 'media_footnotes'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
id = _col(_int, primary_key=True)
media_footnote_details = _relationship('SPIREMediaFootnoteDetail',
backref='media_footnote')
class SPIREMediaFootnoteDetail(BaseModel):
__tablename__ = 'media_footnote_details'
id = _col(_int, primary_key=True)
mf_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.media_footnotes.id'),
nullable=False)
status_control = _col(_text)
start_datetime = _col(_dt, nullable=False)
end_datetime = _col(_dt)
footnote_type = _col(_text, nullable=False)
display_text = _col(_text, nullable=False)
single_footnote_text = _col(_text, nullable=False)
joint_footnote_text = _col(_text)
footnote_entries = _relationship('SPIREFootnoteEntry',
backref='media_footnote_detail')
__table_args__ = (
_check("""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
"""),
_check("footnote_type IN ('STANDARD','END_USER')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class Pipeline(BaseModel):
__tablename__ = 'pipeline'
__table_args__ = (_unique('organisation',
'dataset',
name='organisation_dataset_unique_together'), )
id = _col('id', _int, primary_key=True, autoincrement=True)
organisation = _col(_text, nullable=False)
dataset = _col(_text, nullable=False)
slug = _col(_text, nullable=False)
data_files = _relationship('PipelineDataFile',
order_by='desc(PipelineDataFile.processed_at)')
@staticmethod
def generate_slug(mapper, connection, target):
if not target.slug:
target.slug = slugify(f'{target.organisation} {target.dataset}')
@property
def pipeline_schema(self):
return f'{self.organisation}.{self.dataset}'
@property
def latest_version(self):
# data_files is always sorted by processed_at descending
for data_file in self.data_files:
if data_file.state == DataUploaderFileState.COMPLETED.value:
return data_file
return None
def __str__(self):
return self.pipeline_schema
class SPIRERefReportRating(BaseModel):
__tablename__ = 'ref_report_ratings'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
rating = _col(_text, primary_key=True)
report_rating = _col(_text, nullable=False)
control_entries = _relationship('SPIREControlEntry',
backref='ref_report_rating')
class SPIREApplication(BaseModel):
__tablename__ = 'applications'
ela_grp_id = _col(_int, primary_key=True, autoincrement=True)
case_type = _col(_text, nullable=False)
case_sub_type = _col(_text)
initial_processing_time = _col(_int, nullable=False)
case_closed_date = _col(_dt, nullable=False)
withheld_status = _col(_text)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
ela_id = _col(_int)
application_countries = _relationship('SPIREApplicationCountry',
backref='application')
application_amendments = _relationship('SPIREApplicationAmendment',
backref='application')
goods_incidents = _relationship('SPIREGoodsIncident',
backref='application')
footnote_entries = _relationship('SPIREFootnoteEntry',
backref='application')
incidents = _relationship('SPIREIncident', backref='application')
third_parties = _relationship('SPIREThirdParty', backref='application')
ultimate_end_users = _relationship('SPIREUltimateEndUser',
backref='application')
__table_args__ = (
_check(
"case_type IN ('SIEL', 'OIEL', 'SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')"
),
_check("""
(
case_type = 'SIEL' AND case_sub_type IN ('PERMANENT', 'TEMPORARY', 'TRANSHIPMENT')
)
OR
(
case_type = 'OIEL'
AND case_sub_type IN ('DEALER', 'MEDIA', 'MIL_DUAL', 'UKCONTSHELF','CRYPTO')
)
OR
(
case_type IN ('SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')
AND case_sub_type IS NULL
)
"""),
_check(
"withheld_status IS NULL OR withheld_status IN ('PENDING', 'WITHHELD')"
),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREFootnote(BaseModel):
__tablename__ = 'footnotes'
id = _col(_int, primary_key=True)
text = _col(_text)
status = _col(_text, nullable=False)
footnote_entries = _relationship('SPIREFootnoteEntry', backref='footnote')
__table_args__ = (
_check("status IN ('CURRENT', 'DELETED', 'ARCHIVED')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREBatch(BaseModel):
__tablename__ = 'batches'
id = _col(_int, primary_key=True, autoincrement=True)
batch_ref = _col(_text, nullable=False)
status = _col(_text, nullable=False)
start_date = _col(_dt)
end_date = _col(_dt)
approve_date = _col(_dt, nullable=False)
release_date = _col(_dt)
staging_date = _col(_dt)
application_countries = _relationship('SPIREApplicationCountry',
backref='batch')
applications = _relationship('SPIREApplication', backref='batch')
goods_incidents = _relationship('SPIREGoodsIncident', backref='batch')
footnote_entries = _relationship('SPIREFootnoteEntry', backref='batch')
incidents = _relationship('SPIREIncident', backref='batch')
returns = _relationship('SPIREReturn', backref='batch')
third_parties = _relationship('SPIREThirdParty', backref='batch')
ultimate_end_users = _relationship('SPIREUltimateEndUser', backref='batch')
__table_args__ = (
_check("status IN ('RELEASED','STAGING')"),
_check("""
(
batch_ref LIKE 'C%' AND start_date IS NULL AND end_date IS NULL
)
OR
(
batch_ref NOT LIKE 'C%'
AND start_date IS NOT NULL
AND date_trunc('day', start_date) = start_date
AND end_date IS NOT NULL
AND date_trunc('day', end_date) = end_date
AND start_date <= end_date
)
"""),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class PipelineDataFile(BaseModel):
__tablename__ = 'pipeline_data_file'
id = _col(_uuid(as_uuid=True), primary_key=True, default=uuid.uuid4)
data_file_url = _col(_text, nullable=False)
pipeline_id = _col(_int, _foreign_key('pipeline.id'), nullable=False)
state = _col(_text)
error_message = _col(_text)
uploaded_at = _col(_dt, default=lambda: datetime.datetime.utcnow())
started_processing_at = _col(_dt)
processed_at = _col(_dt)
column_types = _col(_array(_text))
delimiter = _col(_text,
nullable=False,
server_default=DEFAULT_CSV_DELIMITER)
quote = _col(_text, server_default=DEFAULT_CSV_QUOTECHAR)
pipeline = _relationship('Pipeline')