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 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 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 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 SPIREMediaFootnoteCountry(BaseModel):
__tablename__ = 'media_footnote_countries'
id = _col(_int, primary_key=True)
ela_grp_id = _col(_int, nullable=False)
mf_grp_id = _col(_int, nullable=False)
country_id = _col(_int, nullable=False)
country_name = _col(_text, nullable=False)
status_control = _col(_text)
start_datetime = _col(_dt, nullable=False)
end_datetime = _col(_dt)
__table_args__ = (
_check("""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
"""),
{
'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 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 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 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
},
)
