class SPIREThirdParty(BaseModel):
__tablename__ = 'third_parties'
tp_id = _col(_int, primary_key=True)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
sh_id = _col(_int)
country_id = _col(_int, nullable=False)
ultimate_end_user_flag = _col(_int)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
status_control = _col(_text, nullable=False)
__table_args__ = (
_check(
"ultimate_end_user_flag IS NULL OR ultimate_end_user_flag IN (0, 1)"
),
_check("version_no >= 0"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREUltimateEndUser(BaseModel):
__tablename__ = 'ultimate_end_users'
ueu_id = _col(_int, primary_key=True)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
country_id = _col(_int, nullable=False)
status_control = _col(_text, nullable=False)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
sh_id = _col(_int)
ultimate_end_user_flag = _col(_int)
__table_args__ = (
_check("version_no >= 0"),
_check("status_control IN ('A', 'P', 'C', 'D')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
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 SPIREControlEntry(BaseModel):
__tablename__ = 'control_entries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
rating = _col(
_text,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.ref_report_ratings.rating'),
primary_key=True)
value = _col(_decimal)
class SPIREApplicationCountry(BaseModel):
__tablename__ = 'application_countries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
primary_key=True)
country_id = _col(_int, primary_key=True)
report_date = _col(_dt, nullable=False)
start_date = _col(_dt, nullable=False)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
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 SPIREReasonForRefusal(BaseModel):
__tablename__ = 'reasons_for_refusal'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
reason_for_refusal = _col(_text, primary_key=True)
class SPIRECountryGroupEntry(BaseModel):
__tablename__ = 'country_group_entries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
cg_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.country_groups.id'),
primary_key=True)
country_id = _col(_int, primary_key=True)
class SPIREArs(BaseModel):
__tablename__ = 'ars'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
ars_value = _col(_text, primary_key=True)
ars_quantity = _col(_int)
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 SPIREApplicationAmendment(BaseModel):
__tablename__ = 'application_amendments'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
primary_key=True)
ela_id = _col(_int, primary_key=True)
case_type = _col(_text, nullable=False)
case_sub_type = _col(_text)
case_processing_time = _col(_int, nullable=False)
amendment_closed_date = _col(_dt, nullable=False)
withheld_status = _col(_text)
batch_id = _col(_int, nullable=False)
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')
class SPIREReturn(BaseModel):
__tablename__ = 'returns'
elr_id = _col(_int, primary_key=True)
elr_version = _col(_int, primary_key=True)
status = _col(_text, nullable=False)
created_datetime = _col(_dt, nullable=False)
status_control = _col(_text, nullable=False)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
licence_type = _col(_text, nullable=False)
el_id = _col(_int, nullable=False)
ogl_id = _col(_int)
return_period_date = _col(_dt)
end_country_id = _col(_int)
usage_count = _col(_int)
end_user_type = _col(_text)
eco_comment = _col(_text)
__table_args__ = (
_check("elr_version > 0"),
_check("status IN ('WITHDRAWN', 'ACTIVE')"),
_check("status_control IN ('A','P','C')"),
_check("licence_type IN ('OGEL','OIEL','OITCL')"),
_check("""
(
licence_type = 'OGEL' AND ogl_id IS NOT NULL
)
OR
(
licence_type != 'OGEL' AND ogl_id IS NULL
)
"""),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREIncident(BaseModel):
__tablename__ = 'incidents'
inc_id = _col(_int, primary_key=True)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
status = _col(_text, nullable=False)
type = _col(_text, nullable=False)
case_type = _col(_text, nullable=False)
case_sub_type = _col(_text)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
ela_id = _col(_int)
licence_id = _col(_int)
report_date = _col(_dt, nullable=False)
temporary_licence_flag = _col(_int, nullable=False)
licence_conversion_flag = _col(_int, nullable=False)
incorporation_flag = _col(_int, nullable=False)
mil_flag = _col(_int, nullable=False)
other_flag = _col(_int, nullable=False)
torture_flag = _col(_int, nullable=False)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True)
ogl_id = _col(_int)
status_control = _col(_text, nullable=False)
else_id = _col(_int)
stakeholders_confirmed = _col(_text)
__table_args__ = (
_check("status IN ('READY', 'FOR_ATTENTION')"),
_check("version_no >= 0"),
_check("""
(
case_type != 'OGEL' AND ogl_id IS NULL
)
OR
(
case_type = 'OGEL' AND ogl_id IS NOT NULL
)
"""),
_check("temporary_licence_flag IN (0, 1)"),
_check("""
(
type != 'REFUSAL' AND licence_id IS NOT NULL
)
OR
(
type = 'REFUSAL' AND licence_id IS NULL
)
"""),
_check("""
(
type = 'SUSPENSION' AND else_id IS NOT NULL
)
OR
(
type != 'SUSPENSION' AND else_id IS NULL
)
"""),
_check("""
type IN (
'REFUSAL', 'ISSUE', 'REDUCTION', 'REVOKE', 'DEREGISTRATION',
'SUSPENSION', 'SURRENDER'
)
"""),
_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("licence_conversion_flag IN (0, 1)"),
_check("incorporation_flag IN (0, 1)"),
_check("mil_flag IN (0, 1)"),
_check("other_flag IN (0, 1)"),
_check("torture_flag IN (0, 1)"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREFootnoteEntry(BaseModel):
__tablename__ = 'footnote_entries'
fne_id = _col(_int, primary_key=True)
fn_id = _col(_int, _foreign_key(f'{SPIRE_SCHEMA_NAME}.footnotes.id'))
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
goods_item_id = _col(_int)
country_id = _col(_int)
fnr_id = _col(_int)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True, nullable=False)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
status_control = _col(_text, nullable=False)
mfd_id = _col(
_int, _foreign_key(f'{SPIRE_SCHEMA_NAME}.media_footnote_details.id'))
mf_grp_id = _col(_int)
mf_free_text = _col(_text)
__table_args__ = (
_check("""
(
goods_item_id IS NULL AND country_id IS NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NOT NULL AND country_id IS NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NULL AND country_id IS NOT NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NULL AND country_id IS NOT NULL AND fnr_id IS NOT NULL
)
"""),
_check("version_no >= 0"),
_check("""
(
fn_id IS NOT NULL AND mfd_id IS NULL AND mf_free_text IS NULL AND mf_grp_id IS NULL
)
OR
(
fn_id IS NULL
AND mfd_id IS NOT NULL
AND mf_free_text IS NULL
AND mf_grp_id IS NOT NULL
)
OR
(
fn_id IS NULL
AND mfd_id IS NULL
AND mf_free_text IS NOT NULL
AND mf_grp_id IS NOT NULL
)
"""),
{
'schema': SPIRE_SCHEMA_NAME
},
)