def build_single_movement_query(dbsession, owner, period):
"""Build a query that lists the unreconciled movements in open periods.
Return a query providing these columns:
- transfer_id
- date
- delta
- movement_ids
"""
movement_date_c = func.date(
func.timezone(get_tzname(owner), func.timezone('UTC',
FileMovement.ts)))
return (dbsession.query(
TransferRecord.transfer_id,
movement_date_c.label('date'),
file_movement_delta.label('delta'),
array([FileMovement.movement_id]).label('movement_ids'),
).select_from(FileMovement).join(
TransferRecord,
TransferRecord.id == FileMovement.transfer_record_id).join(
Period, Period.id == FileMovement.period_id).filter(
FileMovement.owner_id == owner.id,
FileMovement.file_id == period.file_id,
FileMovement.reco_id == null,
file_movement_delta != 0,
~Period.closed,
))
def __init__(self, owner):
self.table = FileMovement
self.date_c = func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', FileMovement.ts)))
self.id_c = FileMovement.movement_id
self.plural = 'movements'
def list_bundle_records(self):
"""List bundle transfers that contain unreconciled bundled movements.
"""
dbsession = self.dbsession
owner = self.owner
period = self.period
# bundle_transfer_ids_cte lists the bundle transfer IDs
# of the unreconciled bundled movements for this file.
bundle_transfer_ids_cte = (dbsession.query(
TransferRecord.bundle_transfer_id).select_from(FileMovement).join(
TransferRecord,
TransferRecord.id == FileMovement.transfer_record_id).join(
Period, Period.id == FileMovement.period_id).filter(
FileMovement.owner_id == owner.id,
FileMovement.file_id == period.file_id,
FileMovement.reco_id == null,
file_movement_delta != 0,
TransferRecord.bundle_transfer_id != null,
~Period.closed,
).distinct().cte('bundle_transfer_ids_cte'))
record_date_c = func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', TransferRecord.start)))
bundle_records = (dbsession.query(
TransferRecord.transfer_id,
TransferRecord.bundled_transfers,
record_date_c.label('date'),
).filter(
TransferRecord.owner_id == owner.id,
TransferRecord.transfer_id.in_(bundle_transfer_ids_cte),
TransferRecord.bundled_transfers != null,
func.jsonb_array_length(TransferRecord.bundled_transfers) > 0,
).order_by(TransferRecord.start, TransferRecord.transfer_id).all())
log.info("BundleFinder: %s unreconciled bundle(s) for period %s",
len(bundle_records), period.id)
return bundle_records
def push_recos(request, period):
"""Push all the recos in a period to other open periods.
Create a new period if necessary.
This is done in preparation for deleting the period.
"""
dbsession = request.dbsession
owner = request.owner
owner_id = owner.id
assert period.owner_id == owner_id
entry_date_c = (dbsession.query(func.min(AccountEntry.entry_date)).filter(
AccountEntry.reco_id == Reco.id).correlate(Reco).as_scalar())
movement_date_c = (dbsession.query(
func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', func.min(FileMovement.ts))))
).select_from(FileMovement).filter(
FileMovement.reco_id == Reco.id).correlate(Reco).as_scalar())
future = datetime.date.today() + datetime.timedelta(days=366 * 100)
# reco_date_c provides the date of each reco.
# Recos with no entries or movements have no date, so fall back to
# an arbitrary date 100+ years in the future as the reco date.
# The arbitrary date does not get stored (unless the user has created
# a period for 100+ years in the future.)
reco_date_c = func.coalesce(entry_date_c, movement_date_c, future)
# List the dates of all recos in this period.
reco_rows = (dbsession.query(
reco_date_c,
Reco.id).select_from(Reco).filter(Reco.period_id == period.id).all())
if not reco_rows:
# There are no reconciliations in the period.
return 0
# List the other open periods for the peer.
period_list = (dbsession.query(Period).filter(
Period.owner_id == owner_id, Period.file_id == period.file_id,
~Period.closed, Period.id != period.id).all())
# List the recos to reassign.
days = set()
reco_ids = []
for day, reco_id in reco_rows:
days.add(day)
if reco_id is not None:
reco_ids.append(reco_id)
# Map the days to periods.
day_periods, day_period_cte, missing_period = make_day_period_cte(
days=sorted(days), period_list=period_list)
# If no period is available for some of the recos,
# create a new period.
if missing_period:
new_period = add_open_period(request=request,
file_id=period.file_id,
event_type='add_period_for_push_reco',
has_vault=period.has_vault)
new_period_id = new_period.id
else:
new_period_id = None
# Reassign the recos.
subq = (dbsession.query(day_period_cte.c.period_id).filter(
day_period_cte.c.day == reco_date_c).as_scalar())
(dbsession.query(Reco).filter(Reco.id.in_(reco_ids)).update(
{'period_id': func.coalesce(subq, new_period_id)},
synchronize_session='fetch'))
# Reassign the period_id of affected movements.
subq = (dbsession.query(
Reco.period_id).filter(Reco.id == FileMovement.reco_id).as_scalar())
(dbsession.query(FileMovement).filter(
FileMovement.reco_id.in_(reco_ids)).update(
{'period_id': subq}, synchronize_session='fetch'))
# Reassign the period_id of affected account entries.
subq = (dbsession.query(
Reco.period_id).filter(Reco.id == AccountEntry.reco_id).as_scalar())
(dbsession.query(AccountEntry).filter(
AccountEntry.reco_id.in_(reco_ids)).update(
{'period_id': subq}, synchronize_session='fetch'))
dbsession.add(
OwnerLog(owner_id=owner_id,
personal_id=request.personal_id,
event_type='push_recos',
content={
'period_id': period.id,
'file_id': period.file_id,
'reco_ids': reco_ids,
'day_periods': day_periods,
'new_period_id': new_period_id,
}))
return len(reco_ids)
def pull_recos(request, period):
"""Pull recos from other open periods into this period.
"""
dbsession = request.dbsession
owner = request.owner
owner_id = owner.id
assert period.owner_id == owner_id
# reco_filter finds the recos that might need to be pulled.
reco_filter = and_(
Reco.owner_id == owner_id,
Reco.period_id != period.id,
Period.file_id == period.file_id,
~Period.closed,
)
entry_date_c = (dbsession.query(func.min(AccountEntry.entry_date)).filter(
AccountEntry.reco_id == Reco.id).correlate(Reco).as_scalar())
movement_date_c = (dbsession.query(
func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', func.min(FileMovement.ts))))
).filter(FileMovement.reco_id == Reco.id).correlate(Reco).as_scalar())
# reco_date_c provides the date of each reco. Note that
# some recos have no account entries or movements; they have a date
# of None. We don't want to move those recos into this period.
reco_date_c = func.coalesce(entry_date_c, movement_date_c)
# List the dates of all recos in other open periods
# for the same peer loop.
day_rows = (dbsession.query(reco_date_c).select_from(Reco).join(
Period,
Period.id == Reco.period_id).filter(reco_filter).distinct().all())
if not day_rows:
# There are no recos to pull in.
return 0
# List the dates of the recos to pull in.
reassign_days = []
period_list = [period]
for (day, ) in day_rows:
if day is not None and get_period_for_day(
period_list, day, default_endless=False) is period:
reassign_days.append(day)
if not reassign_days:
# None of the recos found should be pulled in to this period.
return 0
# Map the reassignable recos to this period.
# (Recos for other periods will not be listed in day_period_cte.)
day_periods, day_period_cte, missing_period = make_day_period_cte(
days=sorted(reassign_days),
period_list=period_list,
default_endless=False)
# List the recos to reassign.
reco_id_rows = (dbsession.query(Reco.id).join(
Period, Period.id == Reco.period_id).join(
day_period_cte,
day_period_cte.c.day == reco_date_c).filter(reco_filter).all())
reco_ids = [reco_id for (reco_id, ) in reco_id_rows]
# Reassign recos.
(dbsession.query(Reco).filter(Reco.id.in_(reco_ids)).update(
{'period_id': period.id}, synchronize_session='fetch'))
# Reassign the period_id of affected movements.
(dbsession.query(FileMovement).filter(
FileMovement.reco_id.in_(reco_ids)).update(
{'period_id': period.id}, synchronize_session='fetch'))
# Reassign the period_id of affected account entries.
(dbsession.query(AccountEntry).filter(
AccountEntry.reco_id.in_(reco_ids)).update(
{'period_id': period.id}, synchronize_session='fetch'))
dbsession.add(
OwnerLog(owner_id=owner_id,
personal_id=request.personal_id,
event_type='pull_recos',
content={
'period_id': period.id,
'file_id': period.file_id,
'reco_ids': reco_ids,
'day_periods': day_periods,
}))
return len(reco_ids)