def update_account_num(fiscal_year, fiscal_period, only_broken_links=False):
""" Set the account_num on newly SF133 entries. We use raw SQL as sqlalchemy doesn't have operators like OVERLAPS
and IS NOT DISTINCT FROM built in (resulting in a harder to understand query).
Args:
fiscal_year: fiscal year to update TAS IDs for
fiscal_period: fiscal period to update TAS IDs for
only_broken_links: only update ones with a blank account number
"""
sess = GlobalDB.db().session
# number of months since 0AD for this fiscal period
zero_based_period = fiscal_period - 1
absolute_fiscal_month = 12 * fiscal_year + zero_based_period - 3
absolute_following_month = absolute_fiscal_month + 1
start_date = date(absolute_fiscal_month // 12,
(absolute_fiscal_month % 12) + 1, # 1-based
1)
end_date = date(absolute_following_month // 12,
(absolute_following_month % 12) + 1, # 1-based
1)
subquery = matching_cars_subquery(sess, SF133, start_date, end_date)
logger.info("Updating account_nums for Fiscal %s-%s", fiscal_year, fiscal_period)
filters = [SF133.fiscal_year == fiscal_year, SF133.period == fiscal_period]
if only_broken_links:
filters.append(SF133.account_num.is_(None))
sess.query(SF133).\
filter(*filters).\
update({SF133.account_num: subquery}, synchronize_session=False)
sess.commit()
def update_tas_id(fiscal_year, fiscal_period):
"""Set the tas_id on newly SF133 entries. We use raw SQL as sqlalchemy
doesn't have operators like OVERLAPS and IS NOT DISTINCT FROM built in
(resulting in a harder to understand query)."""
sess = GlobalDB.db().session
# number of months since 0AD for this fiscal period
zero_based_period = fiscal_period - 1
absolute_fiscal_month = 12 * fiscal_year + zero_based_period - 3
absolute_following_month = absolute_fiscal_month + 1
start_date = date(
absolute_fiscal_month // 12,
(absolute_fiscal_month % 12) + 1, # 1-based
1)
end_date = date(
absolute_following_month // 12,
(absolute_following_month % 12) + 1, # 1-based
1)
subquery = matching_cars_subquery(sess, SF133, start_date, end_date)
logger.info("Updating tas_ids for Fiscal %s-%s", fiscal_year,
fiscal_period)
sess.query(SF133).\
filter_by(fiscal_year=fiscal_year, period=fiscal_period).\
update({SF133.tas_id: subquery}, synchronize_session=False)
sess.commit()
def update_tas_ids(model_class, submission_id):
sess = GlobalDB.db().session
submission = sess.query(Submission).filter_by(submission_id=submission_id).one()
subquery = matching_cars_subquery(sess, model_class, submission.reporting_start_date, submission.reporting_end_date)
sess.query(model_class).filter_by(submission_id=submission_id).\
update({getattr(model_class, 'tas_id'): subquery}, synchronize_session=False)
sess.commit()
def update_tas_ids(model_class, submission_id):
sess = GlobalDB.db().session
submission = sess.query(Submission).filter_by(submission_id=submission_id).one()
subquery = matching_cars_subquery(sess, model_class, submission.reporting_start_date, submission.reporting_end_date)
sess.query(model_class).filter_by(submission_id=submission_id).\
update({getattr(model_class, 'tas_id'): subquery}, synchronize_session=False)
sess.commit()