def query_data(session, agency_code, agency_type, start, end):
""" Request D2 file data
Args:
session: DB session
agency_code: FREC or CGAC code for generation
agency_type: The type of agency (awarding or funding) to generate the file for
start: Beginning of period for D file
end: End of period for D file
Returns:
The rows using the provided dates for the given agency.
"""
rows = initial_query(session).\
filter(file_model.is_active.is_(True)).\
filter(func.cast_as_date(file_model.action_date) >= start).\
filter(func.cast_as_date(file_model.action_date) <= end)
# Funding or awarding agency filtering
if agency_type == 'funding':
rows = rows.filter(file_model.funding_agency_code == agency_code)
else:
rows = rows.filter(file_model.awarding_agency_code == agency_code)
return rows
def query_data(session, agency_code, agency_type, start, end, page_start,
page_stop):
""" Request D1 file data
Args:
session: DB session
agency_code: FREC or CGAC code for generation
agency_type: The type of agency (awarding or funding) to generate the file for
start: Beginning of period for D file
end: End of period for D file
page_start: Beginning of pagination
page_stop: End of pagination
Returns:
The rows using the provided dates and page size for the given agency.
"""
rows = initial_query(session).\
filter(func.cast_as_date(file_model.action_date) >= start).\
filter(func.cast_as_date(file_model.action_date) <= end)
# Funding or awarding agency filtering
if agency_type == 'funding':
rows = rows.filter(file_model.funding_agency_code == agency_code)
else:
rows = rows.filter(file_model.awarding_agency_code == agency_code)
# Slice the final query
rows = rows.slice(page_start, page_stop)
return rows
def query_data(session, agency_code, agency_type, start, end):
""" Request D2 file data
Args:
session: DB session
agency_code: FREC or CGAC code for generation
agency_type: The type of agency (awarding or funding) to generate the file for
start: Beginning of period for D file
end: End of period for D file
Returns:
The rows using the provided dates for the given agency.
"""
rows = initial_query(session).\
filter(file_model.is_active.is_(True)).\
filter(func.cast_as_date(file_model.action_date) >= start).\
filter(func.cast_as_date(file_model.action_date) <= end)
# Funding or awarding agency filtering
if agency_type == 'funding':
rows = rows.filter(file_model.funding_agency_code == agency_code)
else:
rows = rows.filter(file_model.awarding_agency_code == agency_code)
return rows
def query_data(session, agency_code, start, end, page_start, page_stop):
""" Request D1 file data
Args:
session - DB session
agency_code - FREC or CGAC code for generation
start - Beginning of period for D file
end - End of period for D file
page_start - Beginning of pagination
page_stop - End of pagination
"""
rows = initial_query(session).\
filter(file_model.awarding_agency_code == agency_code).\
filter(func.cast_as_date(file_model.action_date) >= start).\
filter(func.cast_as_date(file_model.action_date) <= end).\
slice(page_start, page_stop)
return rows
def query_data(session, agency_code, start, end, page_start, page_stop):
""" Request D2 file data
Args:
session: DB session
agency_code: FREC or CGAC code for generation
start: Beginning of period for D file
end: End of period for D file
page_start: Beginning of pagination
page_stop: End of pagination
Returns:
The rows using the provided dates and page size for the given agency.
"""
rows = initial_query(session).\
filter(file_model.is_active.is_(True)).\
filter(file_model.awarding_agency_code == agency_code).\
filter(func.cast_as_date(file_model.action_date) >= start).\
filter(func.cast_as_date(file_model.action_date) <= end).\
slice(page_start, page_stop)
return rows
def derive_office_data(obj, office_dict, sess):
""" Deriving office data
Args:
obj: a dictionary containing the details we need to derive from and to
office_dict: a dictionary containing all the data for Office objects keyed by office code
sess: the current DB session
"""
# If we don't have an awarding office code, we need to copy it from the earliest transaction of that award
if not obj['awarding_office_code'] or not obj['funding_office_code']:
first_transaction = None
pafa = PublishedAwardFinancialAssistance
awarding_sub_code = obj['awarding_sub_tier_agency_c'].upper(
) if obj['awarding_sub_tier_agency_c'] else None
if obj['record_type'] == 1:
uri = obj['uri'].upper() if obj['uri'] else None
# Get the minimum action date for this uri/AwardingSubTierCode combo
min_action_date = sess.query(func.min(pafa.action_date).label("min_date")). \
filter(func.upper(pafa.uri) == uri, func.upper(pafa.awarding_sub_tier_agency_c) == awarding_sub_code,
pafa.is_active.is_(True), pafa.record_type == 1).one()
# If we have a minimum action date, get the office codes for the first entry that matches it
if min_action_date.min_date:
first_transaction = sess.query(pafa.awarding_office_code, pafa.funding_office_code,
pafa.award_modification_amendme).\
filter(func.upper(pafa.uri) == uri, pafa.is_active.is_(True),
func.upper(pafa.awarding_sub_tier_agency_c) == awarding_sub_code,
func.cast_as_date(pafa.action_date) == min_action_date.min_date,
pafa.record_type == 1).first()
else:
fain = obj['fain'].upper() if obj['fain'] else None
# Get the minimum action date for this fain/AwardingSubTierCode combo
min_action_date = sess.query(func.min(func.cast(pafa.action_date, DATE)).label("min_date")).\
filter(func.upper(pafa.fain) == fain, func.upper(pafa.awarding_sub_tier_agency_c) == awarding_sub_code,
pafa.is_active.is_(True), pafa.record_type != 1).one()
# If we have a minimum action date, get the office codes for the first entry that matches it
if min_action_date.min_date:
first_transaction = sess.query(pafa.awarding_office_code, pafa.funding_office_code,
pafa.award_modification_amendme).\
filter(func.upper(pafa.fain) == fain, pafa.is_active.is_(True),
func.upper(pafa.awarding_sub_tier_agency_c) == awarding_sub_code,
func.cast_as_date(pafa.action_date) == min_action_date.min_date,
pafa.record_type != 1).first()
# If we managed to find a transaction, copy the office codes into it. Don't copy if the mod is the same because
# we don't want to auto-fill the base record for an award.
if first_transaction and first_transaction.award_modification_amendme != obj[
'award_modification_amendme']:
# No need to copy if new code isn't blank or old code is
if not obj[
'awarding_office_code'] and first_transaction.awarding_office_code:
# Make sure the code we're copying is a valid awarding office code
award_office = office_dict.get(
first_transaction.awarding_office_code.upper())
if award_office and award_office[
'financial_assistance_awards_office']:
obj['awarding_office_code'] = first_transaction.awarding_office_code
if not obj[
'funding_office_code'] and first_transaction.funding_office_code:
# Make sure the code we're copying is a valid funding office code
fund_office = office_dict.get(
first_transaction.funding_office_code.upper())
if fund_office and fund_office['funding_office']:
obj['funding_office_code'] = first_transaction.funding_office_code
# Deriving awarding_office_name based off awarding_office_code
awarding_code = obj['awarding_office_code'].upper(
) if obj['awarding_office_code'] else None
awarding_office_data = office_dict.get(awarding_code)
if awarding_office_data:
obj['awarding_office_name'] = awarding_office_data['office_name']
else:
obj['awarding_office_name'] = None
# Deriving funding_office_name based off funding_office_code
funding_code = obj['funding_office_code'].upper(
) if obj['funding_office_code'] else None
funding_office_data = office_dict.get(funding_code)
if funding_office_data:
obj['funding_office_name'] = funding_office_data['office_name']
else:
obj['funding_office_name'] = None
def derive_office_data(obj, office_dict, sess):
""" Deriving office data
Args:
obj: a dictionary containing the details we need to derive from and to
office_dict: a dictionary containing all the data for Office objects keyed by office code
sess: the current DB session
"""
# If we don't have an awarding office code, we need to copy it from the earliest transaction of that award
if not obj['awarding_office_code'] or not obj['funding_office_code']:
first_transaction = None
pafa = PublishedAwardFinancialAssistance
if obj['record_type'] == 1:
# Get the minimum action date for this uri/AwardingSubTierCode combo
min_action_date = sess.query(func.min(pafa.action_date).label("min_date")). \
filter(pafa.uri == obj['uri'], pafa.awarding_sub_tier_agency_c == obj['awarding_sub_tier_agency_c'],
pafa.is_active.is_(True), pafa.record_type == 1).one()
# If we have a minimum action date, get the office codes for the first entry that matches it
if min_action_date.min_date:
first_transaction = sess.query(pafa.awarding_office_code, pafa.funding_office_code,
pafa.award_modification_amendme).\
filter(pafa.uri == obj['uri'], pafa.is_active.is_(True),
pafa.awarding_sub_tier_agency_c == obj['awarding_sub_tier_agency_c'],
func.cast_as_date(pafa.action_date) == min_action_date.min_date,
pafa.record_type == 1).first()
else:
# Get the minimum action date for this fain/AwardingSubTierCode combo
min_action_date = sess.query(func.min(func.cast(pafa.action_date, DATE)).label("min_date")).\
filter(pafa.fain == obj['fain'], pafa.awarding_sub_tier_agency_c == obj['awarding_sub_tier_agency_c'],
pafa.is_active.is_(True), pafa.record_type != 1).one()
# If we have a minimum action date, get the office codes for the first entry that matches it
if min_action_date.min_date:
first_transaction = sess.query(pafa.awarding_office_code, pafa.funding_office_code,
pafa.award_modification_amendme).\
filter(pafa.fain == obj['fain'], pafa.is_active.is_(True),
pafa.awarding_sub_tier_agency_c == obj['awarding_sub_tier_agency_c'],
func.cast_as_date(pafa.action_date) == min_action_date.min_date,
pafa.record_type != 1).first()
# If we managed to find a transaction, copy the office codes into it. Don't copy if the mod is the same because
# we don't want to auto-fill the base record for an award.
if first_transaction and first_transaction.award_modification_amendme != obj['award_modification_amendme']:
# No need to copy if new code isn't blank or old code is
if not obj['awarding_office_code'] and first_transaction.awarding_office_code:
# Make sure the code we're copying is a valid awarding office code
award_office = office_dict.get(first_transaction.awarding_office_code)
if award_office and award_office['grant_office']:
obj['awarding_office_code'] = first_transaction.awarding_office_code
if not obj['funding_office_code'] and first_transaction.funding_office_code:
# Make sure the code we're copying is a valid funding office code
fund_office = office_dict.get(first_transaction.funding_office_code)
if fund_office and fund_office['funding_office']:
obj['funding_office_code'] = first_transaction.funding_office_code
# Deriving awarding_office_name based off awarding_office_code
awarding_office_data = office_dict.get(obj['awarding_office_code'])
if awarding_office_data:
obj['awarding_office_name'] = awarding_office_data['office_name']
else:
obj['awarding_office_name'] = None
# Deriving funding_office_name based off funding_office_code
funding_office_data = office_dict.get(obj['funding_office_code'])
if funding_office_data:
obj['funding_office_name'] = funding_office_data['office_name']
else:
obj['funding_office_name'] = None
