class CorpName(BaseModel):
"""CorpName entity. Corresponds to the 'corp_name' table.
corp_num VARCHAR2 10 2484908
corp_name_typ_cd CHAR 2 2484908
start_event_id NUMBER 22 2484908
corp_name_seq_num NUMBER 22 2484908
end_event_id NUMBER 22 251437
srch_nme VARCHAR2 35 2484908
corp_nme VARCHAR2 150 2484909
dd_corp_num VARCHAR2 10 11929
"""
__tablename__ = 'corp_name'
corp_num = db.Column(db.String(10), primary_key=True)
corp_name_seq_num = db.Column(db.Integer)
corp_name_typ_cd = db.Column(db.String(2))
start_event_id = db.Column(db.Integer)
end_event_id = db.Column(db.Integer)
srch_nme = db.Column(db.String(35))
corp_nme = db.Column(db.String(150))
dd_corp_num = db.Column(db.String(10))
def __repr__(self):
"""Return string representation of a CorpName entity."""
return 'corp num: {}'.format(self.corp_num)
@staticmethod
def get_corp_name_by_corp_id(corp_id):
"""Get CorpName by corp_num."""
return CorpName.query.filter_by(corp_num=corp_id).order_by(
desc(CorpName.end_event_id))
class Office(BaseModel):
"""
Office entity. Corresponds to the 'office' table.
corp_num VARCHAR2 10 4544141
office_typ_cd CHAR 2 4544141
start_event_id NUMBER 22 4544141
end_event_id NUMBER 22 1578071
mailing_addr_id NUMBER 22 4533953
delivery_addr_id NUMBER 22 4527193
dd_corp_num VARCHAR2 10 23155
email_address VARCHAR2 75 14906
"""
__tablename__ = 'office'
corp_num = db.Column(db.String(10), primary_key=True)
office_typ_cd = db.Column(db.String(2), primary_key=True)
start_event_id = db.Column(db.Integer, primary_key=True)
end_event_id = db.Column(db.Integer)
mailing_addr_id = db.Column(db.Integer)
delivery_addr_id = db.Column(db.Integer)
dd_corp_num = db.Column(db.String(10))
email_address = db.Column(db.String(75))
@staticmethod
def get_offices_by_corp_id(corp_id):
"""Get offices by corp_num."""
return Office.query.filter_by(corp_num=corp_id, end_event_id=None)
class OfficeType(BaseModel): # pylint: disable=too-few-public-methods
"""OfficeType entity. Corresponds to the 'office_type' table.
office_typ_cd CHAR 2 9
short_desc VARCHAR2 15 9
full_desc VARCHAR2 40 9
"""
__tablename__ = 'office_type'
office_typ_cd = db.Column(db.String(2), primary_key=True)
short_desc = db.Column(db.String(15))
full_desc = db.Column(db.String(40))
class PartyType(BaseModel): # pylint: disable=too-few-public-methods
"""PartyType entity. Corresponds to the 'party_type' table.
PARTY_TYP_CD CHAR 3 24
SHORT_DESC VARCHAR2 75 24
FULL_DESC VARCHAR2 250 24
"""
__tablename__ = 'party_type'
party_typ_cd = db.Column(db.String(3), primary_key=True)
short_desc = db.Column(db.String(75))
full_desc = db.Column(db.String(250))
class OfficerType(BaseModel):
"""OfficerType entity. Corresponds to the 'officer_type' table.
officer_typ_cd CHAR 3 9
short_desc VARCHAR2 75 9
full_desc VARCHAR2 125 9
"""
# pylint: disable=too-few-public-methods
__tablename__ = 'officer_type'
officer_typ_cd = db.Column(db.String(3), primary_key=True)
short_desc = db.Column(db.String(75))
full_desc = db.Column(db.String(125))
class FilingType(BaseModel):
"""FilingType entity. Corresponds to the 'filing_type' table.
FILING_TYP_CD CHAR 5 420
FILING_TYP_CLASS VARCHAR2 10 420
SHORT_DESC VARCHAR2 50 420
FULL_DESC VARCHAR2 125 420
"""
# pylint: disable=too-few-public-methods
__tablename__ = 'filing_type'
filing_typ_cd = db.Column(db.String(5), primary_key=True)
filing_typ_class = db.Column(db.String(10))
short_desc = db.Column(db.String(50))
full_desc = db.Column(db.String(125))
class CorpOpState(BaseModel):
"""
A lookup table of states a corporation can be in.
state_typ_cd CHAR 3 31
op_state_typ_cd CHAR 3 31
short_desc VARCHAR2 15 31
full_desc VARCHAR2 40 31
"""
# pylint: disable=too-few-public-methods
__tablename__ = 'corp_op_state'
state_typ_cd = db.Column(db.String(3), primary_key=True)
op_state_typ_cd = db.Column(db.String(3))
short_desc = db.Column(db.String(15))
full_desc = db.Column(db.String(40))
class Filing(BaseModel): # pylint: disable=too-few-public-methods
"""
Filing entity. Corresponds to the 'filing' table.
EVENT_ID NUMBER 22 13775802
FILING_TYP_CD CHAR 5 13775803
EFFECTIVE_DT DATE 7 13775801
CHANGE_DT DATE 7 386466
REGISTRATION_DT DATE 7 0
PERIOD_END_DT DATE 7 5529986
ACCESSION_NUM CHAR 10 0
ARRANGEMENT_IND CHAR 1 8212197
AUTH_SIGN_DT DATE 7 8276
WITHDRAWN_EVENT_ID NUMBER 22 325
ODS_TYP_CD CHAR 2 13119449
DD_EVENT_ID NUMBER 22 670145
ACCESS_CD VARCHAR2 9 4664766
NR_NUM VARCHAR2 10 968307
COURT_APPR_IND CHAR 1 15787
COURT_ORDER_NUM VARCHAR2 255 2069
AGM_DATE DATE 7 582818
NEW_CORP_NUM VARCHAR2 10 5
"""
__tablename__ = 'filing'
event_id = db.Column(db.Integer, primary_key=True)
filing_typ_cd = db.Column(db.String(5))
effective_dt = db.Column(db.Date)
change_dt = db.Column(db.Date)
registration_dt = db.Column(db.Date)
period_end_dt = db.Column(db.Date)
accession_num = db.Column(db.String(10))
arrangement_ind = db.Column(db.String(1))
auth_sign_dt = db.Column(db.Date)
withdrawn_event_id = db.Column(db.Integer)
ods_typ_cd = db.Column(db.String(2))
dd_event_id = db.Column(db.Integer)
access_cd = db.Column(db.String(9))
nr_num = db.Column(db.String(10))
court_appr_ind = db.Column(db.String(1))
court_order_num = db.Column(db.String(255))
agm_date = db.Column(db.Date)
new_corp_num = db.Column(db.String(10))
class CorpState(BaseModel):
"""CorpState entity. Corresponds to the 'corp_state' table.
corp_num VARCHAR2 10 4137221
start_event_id NUMBER 22 4137221
end_event_id NUMBER 22 1930459
state_typ_cd CHAR 3 4137221
dd_corp_num VARCHAR2 10 11443
"""
__tablename__ = 'corp_state'
corp_num = db.Column(db.String(10), primary_key=True)
start_event_id = db.Column(db.Integer)
end_event_id = db.Column(db.Integer)
state_typ_cd = db.Column(db.String(3))
dd_corp_num = db.Column(db.String(10))
@staticmethod
def get_corp_states_by_corp_id(corp_id):
"""Get CorpState by corp_num."""
return CorpState.query.filter(CorpState.corp_num == corp_id,
CorpState.end_event_id == None).all() # noqa: E711 # pylint: disable=singleton-comparison
class OfficesHeld(BaseModel):
"""OfficesHeld entity. Corresponds to the 'offices_held' table.
corp_party_id NUMBER 22 3694791
officer_typ_cd CHAR 3 3694794
dd_corp_party_id NUMBER 22 7
"""
# pylint: disable=too-few-public-methods
__tablename__ = 'offices_held'
corp_party_id = db.Column(db.Integer, primary_key=True)
officer_typ_cd = db.Column(db.String(3), primary_key=True)
dd_corp_party_id = db.Column(db.Integer)
class NickName(BaseModel):
"""NickName table. Note: this table has no pkey."""
__tablename__ = 'nickname'
name_id = db.Column(db.Integer)
name = db.Column(db.String(30), primary_key=True)
@staticmethod
def get_nickname_search_expr(field, value):
"""Nickname search.
Generate an expression to return instances where a field matches any nickname related to the provided value.
"""
aliases = db.session.query(NickName.name).filter(
NickName.name_id == db.session.query(NickName.name_id).filter(
NickName.name == value))
alias_list = list(a[0] for a in aliases)
return func.upper(field).in_(alias_list)
class Corporation(BaseModel):
"""Corporation entity. Corresponds to the 'corporation' table.
corp_num VARCHAR2 10 2206759
corp_frozen_typ_cd CHAR 1 819
corp_typ_cd VARCHAR2 3 2206759
recognition_dts DATE 7 2111082
last_ar_filed_dt DATE 7 1025542
transition_dt DATE 7 240802
bn_9 VARCHAR2 9 1179842
bn_15 VARCHAR2 15 1179165
accession_num VARCHAR2 10 941
CORP_PASSWORD VARCHAR2 300 795500
PROMPT_QUESTION VARCHAR2 100 573423
admin_email VARCHAR2 254 703636
send_ar_ind VARCHAR2 1 1642638
tilma_involved_ind VARCHAR2 1 2196814
tilma_cessation_dt DATE 7 4050
firm_last_image_date DATE 7 51550
os_session NUMBER 22 420543
last_agm_date DATE 7 48416
firm_lp_xp_termination_date DATE 7 7443
last_ledger_dt DATE 7 1
ar_reminder_option VARCHAR2 10 69086
ar_reminder_date VARCHAR2 20 67640
TEMP_PASSWORD VARCHAR2 300 3582
TEMP_PASSWORD_EXPIRY_DATE DATE 7 3582
"""
__tablename__ = 'corporation'
corp_num = db.Column(db.String(10), primary_key=True, unique=True)
corp_frozen_typ_cd = db.Column(db.String(1))
corp_typ_cd = db.Column(db.String(3))
recognition_dts = db.Column(db.Date)
last_ar_filed_dt = db.Column(db.Date)
transition_dt = db.Column(db.Date)
bn_9 = db.Column(db.String(9))
bn_15 = db.Column(db.String(15))
accession_num = db.Column(db.String(10))
admin_email = db.Column(db.String(254))
send_ar_ind = db.Column(db.String(1))
tilma_involved_ind = db.Column(db.String(1))
tilma_cessation_dt = db.Column(db.Date)
firm_last_image_date = db.Column(db.Date)
os_session = db.Column(db.Integer)
last_agm_date = db.Column(db.Date)
firm_lp_xp_termination_date = db.Column(db.Date)
last_ledger_dt = db.Column(db.Date)
ar_reminder_option = db.Column(db.String(10))
ar_reminder_date = db.Column(db.String(20))
def __repr__(self):
"""Return string representation of a Corporation entity."""
return 'corp num: {}'.format(self.corp_num)
@staticmethod
def get_corporation_by_id(corp_id):
"""Get a corporation by id."""
query = Corporation.query.add_columns(
Corporation.corp_num, Corporation.transition_dt,
Corporation.admin_email).filter(Corporation.corp_num == corp_id)
try:
return query.one()
except NoResultFound:
return None
@staticmethod
def search_corporations(args, include_addr=False):
"""Search for Corporations by query (search keyword or corpNum) and sort results."""
query = args.get('query')
sort_type = args.get('sort_type')
sort_value = args.get('sort_value')
search_field = args.get('search_field', 'corpNme')
results = Corporation.query_corporations(query, search_field,
sort_type, sort_value,
include_addr)
return results
@staticmethod
def query_corporations(query,
search_field,
sort_type,
sort_value,
include_addr=False):
"""Construct Corporation search db query."""
results = (
Corporation.query.outerjoin(
CorpName,
and_(
CorpName.end_event_id == None, # noqa # pylint: disable=singleton-comparison
Corporation.corp_num == CorpName.corp_num, # noqa
CorpName.corp_name_typ_cd.in_(('CO', 'NB')), # noqa
),
).outerjoin(
CorpState,
and_(
CorpState.corp_num == Corporation.corp_num, # noqa
CorpState.state_typ_cd == 'ACT', # noqa
CorpState.end_event_id == None, # noqa # pylint: disable=singleton-comparison
),
).outerjoin(
Office,
and_(
Office.corp_num == Corporation.corp_num, # noqa
Office.office_typ_cd != literal_column("'RG'"), # noqa
Office.end_event_id == None, # noqa # pylint: disable=singleton-comparison
),
).outerjoin(Address, Office.mailing_addr_id == Address.addr_id))
if include_addr:
results = results.with_entities(
CorpName.corp_nme,
Corporation.corp_num,
Corporation.corp_typ_cd,
Corporation.recognition_dts,
CorpState.state_typ_cd,
Address.addr_line_1,
Address.addr_line_2,
Address.addr_line_3,
Address.postal_cd,
)
else:
results = results.with_entities(
CorpName.corp_nme,
Corporation.corp_num,
Corporation.corp_typ_cd,
Corporation.recognition_dts,
CorpState.state_typ_cd,
)
if search_field == 'corpNme':
results = results.filter(
# or_(
# TODO: This OR query leads to poor performance. We may need a UI control to
# choose which field to search.
# For now, we only support company names.
# Corporation.corp_num == query.upper(),
CorpName.corp_name_typ_cd == literal_column("'CO'"),
# Doing a full CONTAINS search is quite slow. Use STARTSWITH for this reason.
func.upper(CorpName.corp_nme).like('%' + query.upper() + '%')
# )
)
elif search_field == 'corpNum':
results = results.filter(Corporation.corp_num == query.upper(), )
else:
raise Exception(
'Invalid search field specified: `{}`'.format(search_field))
# Sorting
if sort_type is None:
# Note: The Oracle back-end performs better with UPPER() compared to LOWER() case casting.
results = results.order_by(func.upper(CorpName.corp_nme))
else:
sort_field_str = _sort_by_field(sort_type, sort_value)
results = results.order_by(sort_field_str)
return results
class CorpParty(BaseModel):
"""CorpParty entity. Corresponds to the 'corp_party' table.
corp_party_id NUMBER 22 11748880
mailing_addr_id NUMBER 22 8369745
delivery_addr_id NUMBER 22 7636885
corp_num VARCHAR2 10 11748884
party_typ_cd CHAR 3 11748884
start_event_id NUMBER 22 11748884
end_event_id NUMBER 22 6194691
prev_party_id NUMBER 22 3623071
corr_typ_cd CHAR 1 230615
last_report_dt DATE 7 50
appointment_dt DATE 7 3394297
cessation_dt DATE 7 3071988
last_nme VARCHAR2 30 11397162
middle_nme VARCHAR2 30 2773092
first_nme VARCHAR2 30 11392744
business_nme VARCHAR2 150 369824
bus_company_num VARCHAR2 15 108582
email_address VARCHAR2 254 10442
corp_party_seq_num NUMBER 22 27133
OFFICE_NOTIFICATION_DT DATE 7 8380
phone VARCHAR2 30 4306
reason_typ_cd VARCHAR2 3 0
"""
__tablename__ = 'corp_party'
corp_party_id = db.Column(db.Integer, primary_key=True)
mailing_addr_id = db.Column(db.Integer)
delivery_addr_id = db.Column(db.Integer)
corp_num = db.Column(db.String(10))
party_typ_cd = db.Column(db.String(3))
start_event_id = db.Column(db.Integer)
end_event_id = db.Column(db.Integer)
prev_party_id = db.Column(db.Integer)
corr_typ_cd = db.Column(db.String(1))
last_report_dt = db.Column(db.Date)
appointment_dt = db.Column(db.Date)
cessation_dt = db.Column(db.Date)
last_nme = db.Column(db.String(30))
middle_nme = db.Column(db.String(30))
first_nme = db.Column(db.String(30))
business_nme = db.Column(db.String(150))
bus_company_num = db.Column(db.String(15))
email_address = db.Column(db.String(254))
corp_party_seq_num = db.Column(db.Integer)
phone = db.Column(db.String(30))
reason_typ_cd = db.Column(db.String(3))
def __repr__(self):
"""Return string representation of a CorpParty entity."""
return 'corp num: {}'.format(self.corp_party_id)
@staticmethod
def get_corp_party_by_id(corp_party_id):
"""Get a CorpParty entity by id."""
corp_party = CorpParty.query.filter(
CorpParty.corp_party_id == int(corp_party_id))
try:
return corp_party.one()
except NoResultFound:
return None
@staticmethod
def get_corporation_info_by_corp_party_id(corp_party_id):
"""Get Corporation info by CorpParty id."""
# local import to prevent circular import
from search_api.models.corporation import Corporation # pylint: disable=import-outside-toplevel, cyclic-import
query = (CorpParty.query.filter(
CorpParty.corp_party_id == int(corp_party_id)).join(
Corporation,
Corporation.corp_num == CorpParty.corp_num).add_columns(
Corporation.corp_typ_cd, Corporation.corp_admin_email))
try:
return query.one()
except NoResultFound:
return None
@staticmethod
def get_filing_description_by_corp_party_id(corp_party_id):
"""Get FilingType info by CorpParty id."""
return (CorpParty.query.join(
Event, Event.event_id == CorpParty.start_event_id).join(
Filing, Filing.event_id == Event.event_id).join(
FilingType, FilingType.filing_typ_cd == Filing.
filing_typ_cd).add_columns(FilingType.full_desc).filter(
CorpParty.corp_party_id == int(corp_party_id)).all())
@staticmethod
def get_offices_held_by_corp_party_id(corp_party_id):
"""Get OfficesHeld info by CorpParty id."""
return (CorpParty.query.join(
OfficesHeld,
OfficesHeld.corp_party_id == CorpParty.corp_party_id).join(
OfficerType,
OfficerType.officer_typ_cd == OfficesHeld.officer_typ_cd).join(
Event,
Event.event_id == CorpParty.start_event_id).add_columns(
CorpParty.corp_party_id,
OfficerType.officer_typ_cd,
OfficerType.short_desc,
CorpParty.appointment_dt,
Event.event_timestmp,
).filter(
CorpParty.corp_party_id == int(corp_party_id)).all())
@staticmethod
def get_corp_party_at_same_addr(corp_party_id):
"""Get CorpParty entities at the same mailing or delivery address."""
person = CorpParty.get_corp_party_by_id(corp_party_id)
if not person:
return None
# one or both addr may be null, handle each case.
if person.delivery_addr_id or person.mailing_addr_id:
if person.delivery_addr_id and person.mailing_addr_id:
expr = (CorpParty.delivery_addr_id == person.delivery_addr_id
) | (CorpParty.mailing_addr_id
== person.mailing_addr_id)
elif person.delivery_addr_id:
expr = CorpParty.delivery_addr_id == person.delivery_addr_id
elif person.mailing_addr_id:
expr = CorpParty.mailing_addr_id == person.mailing_addr_id
same_addr = (CorpParty.query.join(
Event, Event.event_id == CorpParty.start_event_id).add_columns(
Event.event_timestmp).filter(expr))
else:
same_addr = []
return same_addr
@staticmethod
def get_corp_party_same_name_at_same_addr(corp_party_id):
"""Get CorpParty entities with the same CorpParty name and delivery or mailing address."""
person = CorpParty.get_corp_party_by_id(corp_party_id)
if not person:
return None
same_name_and_company = CorpParty.query.join(
Event, Event.event_id == CorpParty.start_event_id).add_columns(
Event.event_timestmp)
if person.first_nme:
same_name_and_company = same_name_and_company.filter(
CorpParty.first_nme.ilike(person.first_nme))
if person.last_nme:
same_name_and_company = same_name_and_company.filter(
CorpParty.last_nme.ilike(person.last_nme))
if person.corp_num:
same_name_and_company = same_name_and_company.filter(
CorpParty.corp_num.ilike(person.corp_num))
return same_name_and_company
@staticmethod
def search_corp_parties(args):
"""Search for CorpParty entities.
Querystring parameters as follows:
You may provide any number of querystring triples such as
field=ANY_NME|first_nme|last_nme|<any column name>
&operator=exact|contains|startswith|endswith
&value=<string>
&sort_type=asc|desc
&sort_value=ANY_NME|first_nme|last_nme|<any column name>
&additional_cols=address|active|none
For example, to get everyone who has any name that starts with 'Sky', or last name must be exactly 'Little', do:
curl "http://localhost/api/v1/directors/?field=ANY_NME&operator=startswith&value=Sky&field=last_nme&operator=exact&value=Little&mode=ALL" # noqa
"""
fields = args.getlist('field')
operators = args.getlist('operator')
values = args.getlist('value')
# Only triples of clauses are allowed. So, the same number of fields, ops and values.
if len(fields) != len(operators) or len(operators) != len(values):
raise Exception(
'mismatched query param lengths: fields:{} operators:{} values:{}'
.format(len(fields), len(operators), len(values)))
results = CorpParty.query_corp_parties(args)
return results
@staticmethod
def query_corp_parties(args):
"""Construct db query for CorpParty search."""
# local import to prevent circular import
from search_api.models.corporation import Corporation # pylint: disable=import-outside-toplevel, cyclic-import
fields = args.getlist('field')
operators = args.getlist('operator')
values = args.getlist('value')
mode = args.get('mode')
sort_type = args.get('sortType')
sort_value = args.get('sortValue')
additional_cols = args.get('additionalCols')
# Zip the lists, so ('last_nme', 'first_nme') , ('contains', 'exact'), ('Sky', 'Apple') =>
# (('last_nme', 'contains', 'Sky'), ('first_nme', 'exact', 'Apple'))
clauses = list(zip(fields, operators, values))
eventA = aliased(Event)
eventB = aliased(Event)
results = (
CorpParty.query.
join(Corporation, Corporation.corp_num == CorpParty.corp_num).join(
PartyType,
and_(PartyType.party_typ_cd == CorpParty.party_typ_cd),
).join(
CorpState,
and_(
CorpState.corp_num == CorpParty.corp_num,
CorpState.end_event_id == None, # pylint: disable=singleton-comparison
),
).outerjoin(
CorpName,
and_(
CorpName.end_event_id == None, # pylint: disable=singleton-comparison
# CorpName should be "Corporation" or "Number BC Company"
CorpName.corp_name_typ_cd.in_(('CO', 'NB')),
Corporation.corp_num == CorpName.corp_num,
),
).outerjoin(
Address,
and_(Address.addr_id == CorpParty.mailing_addr_id),
full=True).join(
eventA,
and_(eventA.event_id == CorpParty.start_event_id),
).outerjoin(
eventB,
and_(eventB.event_id == CorpParty.end_event_id),
full=True).outerjoin(
OfficesHeld, OfficesHeld.corp_party_id ==
CorpParty.corp_party_id).outerjoin(
OfficerType, OfficerType.officer_typ_cd ==
OfficesHeld.officer_typ_cd).with_entities(
CorpParty.corp_party_id,
CorpParty.first_nme,
CorpParty.middle_nme,
CorpParty.last_nme,
eventA.event_timestmp.label('appointment_dt'),
eventB.event_timestmp.label('cessation_dt'),
CorpParty.corp_num,
(PartyType.short_desc + " " +
OfficerType.short_desc).label('party_typ_cd'),
CorpName.corp_nme,
Corporation.corp_admin_email,
))
results = CorpParty.add_additional_cols_to_search_query(
additional_cols, fields, results)
# Determine if we will combine clauses with OR or AND. mode=ALL means we use AND. Default mode is OR
if mode == 'ALL':
def filter_reducer(accumulator, filter_value):
return accumulator & _get_filter(*filter_value)
else:
def filter_reducer(accumulator, filter_value):
return accumulator | _get_filter(*filter_value)
# We use reduce here to join all the items in clauses with the & operator or the | operator.
# Similar to if we did "|".join(clause), but calling the boolean operator instead.
filter_grp = reduce(filter_reducer, clauses[1:],
_get_filter(*clauses[0]))
results = results.filter(filter_grp)
# Sorting
if sort_type is None:
results = results.order_by(func.upper(CorpParty.last_nme),
CorpParty.corp_num)
else:
sort_field = _sort_by_field(sort_type, sort_value)
results = results.order_by(sort_field)
return results
@staticmethod
def add_additional_cols_to_search_query(additional_cols, fields, query):
"""Add Address or CorpOpState columns to query based on the additional columns toggle."""
if _is_addr_search(
fields) or additional_cols == ADDITIONAL_COLS_ADDRESS:
query = query.outerjoin(
Address, CorpParty.mailing_addr_id == Address.addr_id)
query = query.add_columns(Address.addr_line_1, Address.addr_line_2,
Address.addr_line_3, Address.city,
Address.postal_cd, Address.address_desc)
if additional_cols == ADDITIONAL_COLS_ACTIVE:
state_type_case_stmt = case(
[
(CorpOpState.state_typ_cd == 'ACT', 'ACTIVE'),
],
else_='HISTORICAL').label("state_typ_cd")
query = query.join(
CorpOpState,
CorpOpState.state_typ_cd == CorpState.state_typ_cd)
query = query.add_columns(state_type_case_stmt)
return query
@staticmethod
def add_additional_cols_to_search_results(additional_cols, fields, row):
"""Add Address or CorpOpState columns to search results based on the additional columns toggle."""
additional_result_columns = {}
if _is_addr_search(
fields) or additional_cols == ADDITIONAL_COLS_ADDRESS:
additional_result_columns['addr'] = _merge_addr_fields(row)
additional_result_columns['postalCd'] = row.postal_cd
if additional_cols == ADDITIONAL_COLS_ACTIVE:
additional_result_columns['stateTypCd'] = row.state_typ_cd
return additional_result_columns
class Address(BaseModel):
"""Address entity. Corresponds to the 'address' table.
addr_id NUMBER 22 20233825
province CHAR 2 18872463
country_typ_cd CHAR 2 19016927
postal_cd VARCHAR2 15 18825296
addr_line_1 VARCHAR2 50 16862093
addr_line_2 VARCHAR2 50 3609613
addr_line_3 VARCHAR2 50 482762
city VARCHAR2 40 17557057
address_format_type VARCHAR2 10 3632701
address_desc VARCHAR2 300 3372387
address_desc_short VARCHAR2 300 3350206
delivery_instructions VARCHAR2 80 34510
unit_no VARCHAR2 6 699964
unit_type VARCHAR2 10 11488
civic_no VARCHAR2 6 2210964
civic_no_suffix VARCHAR2 10 15768
street_name VARCHAR2 30 2221177
street_type VARCHAR2 10 2167805
street_direction VARCHAR2 10 292073
lock_box_no VARCHAR2 5 115988
installation_type VARCHAR2 10 47289
installation_name VARCHAR2 30 47036
installation_qualifier VARCHAR2 15 69
route_service_type VARCHAR2 10 146477
route_service_no VARCHAR2 4 27530
province_state_name VARCHAR2 30 362
"""
__tablename__ = 'address'
addr_id = db.Column(db.Integer, primary_key=True)
province = db.Column(db.String(2))
country_typ_cd = db.Column(db.String(2))
postal_cd = db.Column(db.String(15))
addr_line_1 = db.Column(db.String(50))
addr_line_2 = db.Column(db.String(50))
addr_line_3 = db.Column(db.String(50))
city = db.Column(db.String(40))
address_format_type = db.Column(db.String(10))
address_desc = db.Column(db.String(300))
address_desc_short = db.Column(db.String(300))
delivery_instructions = db.Column(db.String(80))
unit_no = db.Column(db.String(6))
unit_type = db.Column(db.String(10))
civic_no = db.Column(db.String(6))
civic_no_suffix = db.Column(db.String(10))
street_name = db.Column(db.String(30))
street_type = db.Column(db.String(10))
street_direction = db.Column(db.String(10))
lock_box_no = db.Column(db.String(5))
installation_type = db.Column(db.String(10))
installation_name = db.Column(db.String(30))
installation_qualifier = db.Column(db.String(15))
route_service_type = db.Column(db.String(10))
route_service_no = db.Column(db.String(4))
province_state_name = db.Column(db.String(30))
@staticmethod
def get_address_by_id(address_id):
"""Get an Address by id."""
return Address.query.filter(Address.addr_id == address_id).add_columns(
Address.addr_line_1,
Address.addr_line_2,
Address.addr_line_3,
Address.postal_cd,
Address.city,
Address.province,
Address.country_typ_cd,
Address.address_desc,
).one()[0]
@staticmethod
def normalize_addr(address_id):
"""Merge Address fields into a standardized format of street address, city, province, and postal code."""
if not address_id:
return ''
address = Address.get_address_by_id(address_id)
def address_reducer(accumulator, address_field):
if address_field:
return ((accumulator or '') +
', ' if accumulator else '') + (address_field or '')
return accumulator or ''
return reduce(address_reducer, [
address.addr_line_1, address.addr_line_2, address.addr_line_3,
address.city, address.province, address.country_typ_cd,
address.address_desc
])