def __call__(self): request = self.request dbsession = request.dbsession owner = request.owner owner_id = owner.id schema = StatementUploadSchema() try: self.appstruct = appstruct = schema.deserialize(request.json) except colander.Invalid as e: handle_invalid(e, schema=schema) name = appstruct['name'] pos = name.rfind('.') if pos >= 0: ext = name[pos:].lower() else: ext = '' content_type = appstruct['type'].split(';')[0] if content_type in self.excel_types or ext in self.excel_extensions: self.handle_excel() if self.statement is None: raise HTTPBadRequest( json_body={ 'error': 'file_type_not_supported', 'error_description': ("File type not supported: %s (%s)" % (ext, appstruct['type'])), }) # Auto-reconcile the statement to the extent possible. configure_dblog(request=request, event_type='statement_auto_reco') auto_reco_statement(dbsession=dbsession, owner=owner, period=self.context.period, statement=self.statement) entry_count = (dbsession.query( func.count(1)).select_from(AccountEntry).filter( AccountEntry.statement_id == self.statement.id).scalar()) dbsession.add( OwnerLog( owner_id=owner_id, personal_id=request.personal_id, event_type='statement_upload', remote_addr=request.remote_addr, user_agent=request.user_agent, content={ 'statement_id': self.statement.id, 'filename': appstruct['name'], 'content_type': appstruct['type'], 'size': appstruct['size'], 'entry_count': entry_count, }, )) return {'statement': serialize_statement(self.statement)}
def _get_profile_id_for_token(self, request, token): if not token: return None now = datetime.datetime.utcnow() entry = self.token_cache.get(token) if entry is not None: if now < entry['valid_until']: request.wallet_info = entry['wallet_info'] return entry['id'] wallet_info = self._request_wallet_info(request, token) if wallet_info: # This token hasn't actually expired yet. profile_info = wallet_info['profile'] profile_id = profile_info['id'] self.token_cache[token] = { 'id': profile_id, 'valid_until': now + self.cache_duration, 'wallet_info': wallet_info, } request.wallet_info = wallet_info return profile_id else: # This token expired. # Take an opportunity to clean up the token cache. to_delete = [] for token, entry1 in self.token_cache.items(): if now >= entry1['valid_until']: to_delete.append(token) for token in to_delete: self.token_cache.pop(token, None) return None wallet_info = self._request_wallet_info(request, token) if wallet_info is not None: profile_info = wallet_info['profile'] profile_id = profile_info['id'] self.token_cache[token] = { 'id': profile_id, 'valid_until': now + self.cache_duration, 'wallet_info': wallet_info, } request.wallet_info = wallet_info request.owner # Add the Owner to the database request.dbsession.add(OwnerLog( owner_id=profile_id, personal_id=request.personal_id, event_type='access', remote_addr=request.remote_addr, user_agent=request.user_agent, content={'title': profile_info['title']}, )) return profile_id return None
def owner(request): """Get or create the Owner row for the authenticated profile.""" authenticated_userid = request.authenticated_userid if not authenticated_userid: return None dbsession = request.dbsession owner = ( dbsession.query(Owner) .filter_by(id=authenticated_userid) .first()) if owner is None: owner_info = request.wallet_info['profile'] # Insert without creating a conflict with concurrent requests. values = { 'id': owner_info['id'], 'title': owner_info['title'], 'username': owner_info['username'] or '', } stmt = ( sqlalchemy.dialects.postgresql.insert( Owner.__table__, bind=dbsession).values(**values) .on_conflict_do_nothing()) dbsession.execute(stmt) # Now the owner should exist. owner = ( dbsession.query(Owner) .filter_by(id=authenticated_userid) .one()) dbsession.add(OwnerLog( owner_id=owner.id, personal_id=request.personal_id, event_type='created', remote_addr=request.remote_addr, user_agent=request.user_agent, content={'title': owner.title}, )) else: now = datetime.datetime.utcnow() if now - owner.last_update >= datetime.timedelta(seconds=60 * 15): # Update the owner's title and username. wallet_info = request.wallet_info profile_info = wallet_info['profile'] if owner.title != profile_info['title']: owner.title = profile_info['title'] username = profile_info['username'] or '' if owner.username != username: owner.username = username owner.last_update = now return owner
def add_open_period(request, file_id, event_type): """Add a new period. Base it on the end date and end balances of the period with the newest end date. """ dbsession = request.dbsession owner = request.owner owner_id = owner.id prev = (dbsession.query(Period).filter( Period.owner_id == owner_id, Period.file_id == file_id, Period.end_date != null, ).order_by(Period.end_date.desc()).first()) if prev is not None: next_start_date = prev.end_date + datetime.timedelta(days=1) if prev.closed: next_start_circ = prev.end_circ next_start_surplus = prev.end_surplus else: # Compute the previous end_circ and end_surplus. totals = compute_period_totals(dbsession=dbsession, owner_id=owner_id, period_ids=[prev.id])[prev.id] next_start_circ = totals['end']['circ'] next_start_surplus = totals['end']['surplus'] else: next_start_date = None next_start_circ = 0 next_start_surplus = 0 period = Period(owner_id=owner_id, file_id=file_id, start_date=next_start_date, start_circ=next_start_circ, start_surplus=next_start_surplus) dbsession.add(period) dbsession.flush() # Assign period.id dbsession.add( OwnerLog(owner_id=owner_id, personal_id=request.personal_id, event_type=event_type, content={ 'period_id': period.id, 'file_id': file_id, 'start_date': next_start_date, 'start_circ': next_start_circ, 'start_surplus': next_start_surplus, })) return period
def file_unarchive(context, request): """Unarchive the file. """ file = context.file file.archived = False request.dbsession.add(OwnerLog( owner_id=request.owner.id, personal_id=request.personal_id, event_type='unarchive_file', content={ 'file_id': file.id, 'archived': False, })) return serialize_file(file)
def file_archive(context, request): """Archive the file. This merely marks the file as archived, preventing changes until the user unarchives it. """ file = context.file file.archived = True request.dbsession.add(OwnerLog( owner_id=request.owner.id, personal_id=request.personal_id, event_type='archive_file', content={ 'file_id': file.id, 'archived': True, })) return serialize_file(file)
def file_save(context, request): """Change the file.""" file = context.file schema = FileSaveSchema() try: appstruct = schema.deserialize(request.json) except colander.Invalid as e: handle_invalid(e, schema=schema) file.title = appstruct['title'] if file.file_type == 'closed_circ': file.auto_enable_loops = appstruct['auto_enable_loops'] if appstruct['reinterpret']: # Reinterpret all the movements in this file. # This may make some movements reconcileable and may make # reconciliation unavailable for some unreconciled movements. # It does not remove reconciled movements. dbsession = request.dbsession query = ( dbsession.query(FileSync).filter(FileSync.file_id == file.id)) query.delete(synchronize_session=False) dbsession.expire_all() sync = SyncBase(request) sync.sync_missing() request.dbsession.add(OwnerLog( owner_id=request.owner.id, personal_id=request.personal_id, event_type='edit_file', content={ 'file_id': file.id, 'title': file.title, 'auto_enable_loops': appstruct.get('auto_enable_loops'), 'reinterpret': appstruct['reinterpret'], })) return serialize_file(file)
def set_tzname(request): tzname = request.json.get('tzname') if tzname not in pytz.all_timezones: raise HTTPBadRequest(json_body={ 'error': 'unrecognized_time_zone', }) owner = request.owner owner.tzname = tzname request.dbsession.add( OwnerLog( owner_id=owner.id, personal_id=request.personal_id, event_type='tzname_change', remote_addr=request.remote_addr, user_agent=request.user_agent, content={ 'tzname': tzname, }, )) return settings_api(request)
def statement_add_blank(context, request): """Add a blank statement.""" period = context.period dbsession = request.dbsession owner = request.owner owner_id = owner.id schema = StatementAddBlankSchema() try: appstruct = schema.deserialize(request.json) except colander.Invalid as e: handle_invalid(e, schema=schema) statement = Statement( owner_id=owner_id, period_id=period.id, file_id=period.file_id, source=appstruct['source'], ) dbsession.add(statement) dbsession.flush() # Assign statement.id dbsession.add( OwnerLog( owner_id=owner_id, personal_id=request.personal_id, event_type='statement_add_blank', remote_addr=request.remote_addr, user_agent=request.user_agent, content={ 'statement_id': statement.id, 'source': appstruct['source'], }, )) return { 'statement': serialize_statement(statement), }
def set_tzname(self): """If the owner doesn't have a tzname yet, try to set it.""" request = self.request owner = self.owner if not owner.tzname: try: params = self.request.json except Exception: params = {} tzname = params.get('tzname', '').strip() if tzname and tzname in pytz.all_timezones: owner.tzname = tzname request.dbsession.add( OwnerLog( owner_id=owner.id, personal_id=request.personal_id, event_type='tzname_init', remote_addr=request.remote_addr, user_agent=request.user_agent, content={ 'tzname': tzname, }, ))
def statement_delete(context, request): """Delete a statement and the contained account entries.""" period = context.period dbsession = request.dbsession owner = request.owner owner_id = owner.id schema = StatementDeleteSchema() try: appstruct = schema.deserialize(request.json) except colander.Invalid as e: handle_invalid(e, schema=schema) statement = (dbsession.query(Statement).filter( Statement.owner_id == owner_id, Statement.id == appstruct['id'], Statement.file_id == period.file_id, ).first()) if statement is None: raise HTTPBadRequest( json_body={ 'error': 'statement_not_found', 'error_description': ("Statement %s not found." % appstruct['id']), }) delete_conflicts = get_delete_conflicts(dbsession=dbsession, statement=statement) if delete_conflicts: raise HTTPBadRequest( json_body={ 'error': 'statement_delete_conflict', 'error_description': ( "The statement can not be deleted for the following " "reasons: %s" % delete_conflicts), }) # Indicate that entries are being deleted and movements are being # changed because the statement is being deleted. configure_dblog(request=request, event_type='statement_delete') # reco_ids represents the list of recos to empty. reco_ids = (dbsession.query(AccountEntry.reco_id).filter( AccountEntry.statement_id == statement.id, ).distinct().subquery( name='reco_ids_subq')) # Cancel the reco_id of movements reconciled with any entry # in the statement. (dbsession.query(FileMovement).filter( FileMovement.reco_id.in_(reco_ids), ).update( { 'reco_id': None, # Also reset the surplus_delta for each movement. 'surplus_delta': -FileMovement.wallet_delta, }, synchronize_session='fetch')) # Cancel the reco_id of account entries on other statements # reconciled with any entry in the statement. (dbsession.query(AccountEntry).filter( AccountEntry.reco_id.in_(reco_ids), AccountEntry.statement_id != statement.id, ).update({ 'reco_id': None, }, synchronize_session='fetch')) # Delete the account entries, but leave the account entry logs. (dbsession.query(AccountEntry).filter( AccountEntry.statement_id == statement.id, ).delete( synchronize_session='fetch')) # Delete the statement. (dbsession.query(Statement).filter( Statement.id == statement.id, ).delete(synchronize_session='fetch')) request.dbsession.add( OwnerLog( owner_id=owner_id, personal_id=request.personal_id, event_type='statement_delete', remote_addr=request.remote_addr, user_agent=request.user_agent, content=appstruct, )) return {}
def statement_save(context, request): """Save changes to a statement.""" period = context.period dbsession = request.dbsession owner = request.owner owner_id = owner.id schema = StatementSaveSchema() try: appstruct = schema.deserialize(request.json) except colander.Invalid as e: handle_invalid(e, schema=schema) statement = (dbsession.query(Statement).filter( Statement.owner_id == owner_id, Statement.id == appstruct['id'], Statement.file_id == period.file_id, ).first()) if statement is None: raise HTTPBadRequest( json_body={ 'error': 'statement_not_found', 'error_description': ("Statement %s not found." % appstruct['id']), }) new_period = None if statement.period_id != appstruct['period_id']: new_period = (dbsession.query(Period).filter( Period.owner_id == owner_id, Period.file_id == period.file_id, ~Period.closed, Period.id == appstruct['period_id'], ).first()) if new_period is None: raise HTTPBadRequest( json_body={ 'error': 'invalid_period_id', 'error_description': ( "The selected period is closed or not available."), }) changes = {} if statement.source != appstruct['source']: changes['source'] = appstruct['source'] statement.source = appstruct['source'] if new_period is not None: changes['period_id'] = appstruct['period_id'] old_period_id = statement.period_id statement.period_id = appstruct['period_id'] # Change the period of the statement's account entries and recos that # should move with the statement. configure_dblog(request=request, event_type='reassign_statement_period') reassign_statement_period(dbsession=dbsession, statement=statement, old_period_id=old_period_id, new_period_id=statement.period_id) dbsession.add( OwnerLog( owner_id=owner_id, personal_id=request.personal_id, event_type='statement_save', remote_addr=request.remote_addr, user_agent=request.user_agent, content=appstruct, )) return { 'statement': serialize_statement(statement), }
def __call__(self): request = self.request owner = self.owner self.set_tzname() if owner.first_sync_ts is None: # Start a new sync. Download transfers created or changed # after 5 minutes before the last sync. (Add 5 minutes in # case some transfers showed up out of order.) if owner.last_sync_ts is not None: sync_ts = (owner.last_sync_ts - datetime.timedelta(seconds=60 * 5)) else: sync_ts = datetime.datetime(1970, 1, 1) sync_transfer_id = None count_remain = True else: # A sync was started but not finished. Download the next batch. sync_ts = owner.last_sync_ts sync_transfer_id = owner.last_sync_transfer_id count_remain = False sync_ts_iso = sync_ts.isoformat() + 'Z' transfers_download = self.download_batch( sync_ts_iso=sync_ts_iso, sync_transfer_id=sync_transfer_id, count_remain=count_remain) dbsession = request.dbsession more = transfers_download['more'] now = datetime.datetime.utcnow() if more: len_results = len(transfers_download['results']) if owner.first_sync_ts is None: owner.first_sync_ts = to_datetime( transfers_download['first_sync_ts']) owner.sync_total = len_results + transfers_download['remain'] owner.sync_done = len_results else: owner.sync_done += len_results owner.last_sync_ts = to_datetime( transfers_download['last_sync_ts']) owner.last_sync_transfer_id = ( transfers_download['results'][-1]['id']) # Note: avoid division by zero. progress_percent = min( 99, int(100.0 * owner.sync_done / owner.sync_total if owner.sync_total else 0.0)) else: owner.first_sync_ts = None owner.last_sync_transfer_id = None owner.last_sync_ts = now owner.sync_total = 0 owner.sync_done = 0 progress_percent = 100 opn_download = OPNDownload( owner_id=owner.id, content={ 'transfers': transfers_download, 'more': more, }, ) dbsession.add(opn_download) dbsession.flush() self.opn_download_id = opn_download.id dbsession.add( OwnerLog( owner_id=owner.id, personal_id=request.personal_id, event_type='opn_sync', remote_addr=request.remote_addr, user_agent=request.user_agent, content={ 'sync_ts': sync_ts_iso, 'progress_percent': progress_percent, 'change_count': len(self.change_log), 'transfers': { 'ids': sorted(t['id'] for t in transfers_download['results']), 'count': len(transfers_download['results']), 'more': more, 'first_sync_ts': transfers_download['first_sync_ts'], 'last_sync_ts': transfers_download['last_sync_ts'], } }, )) try: self.import_transfer_records(transfers_download) if not more: self.sync_missing() except VerificationFailure as e: # HTTP Error 507 is reasonably close to 'data verification error'. raise HTTPInsufficientStorage(json_body={ 'error': 'verification_failure', 'error_description': str(e), }) return { 'progress_percent': progress_percent, 'change_count': len(self.change_log), 'download_count': len(transfers_download['results']), 'more': more, 'first_sync_ts': transfers_download['first_sync_ts'], 'last_sync_ts': transfers_download['last_sync_ts'], }
def push_unreco(request, period, op): """Push the unreconciled movements or entries to other open periods. Create a new period if necessary. """ dbsession = request.dbsession owner = request.owner owner_id = owner.id assert period.owner_id == owner_id item_filter = and_( op.table.owner_id == owner_id, op.table.file_id == period.file_id, op.table.period_id == period.id, op.table.reco_id == null, ) # List the dates of all items in the period. unreco_query = (dbsession.query( op.date_c.label('day'), op.id_c.label('item_id'), ).filter(item_filter)) unreco_rows = unreco_query.all() if not unreco_rows: # There are no unreconciled items in the period. return 0 # List the other open periods for the file. 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 items to reassign. days = set() item_ids = [] for day, item_id in unreco_rows: days.add(day) if item_id is not None: item_ids.append(item_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 items, # 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_unreco_%s' % op.plural) new_period_id = new_period.id else: new_period_id = None # Reassign the items. subq = (dbsession.query(day_period_cte.c.period_id).filter( day_period_cte.c.day == op.date_c).as_scalar()) (dbsession.query(op.table).filter(item_filter).update( {'period_id': func.coalesce(subq, new_period_id)}, synchronize_session='fetch')) dbsession.add( OwnerLog(owner_id=owner_id, personal_id=request.personal_id, event_type='push_unreco_%s' % op.plural, content={ 'period_id': period.id, 'file_id': period.file_id, 'item_ids': item_ids, 'day_periods': day_periods, 'new_period_id': new_period_id, })) return len(item_ids)
def import_peer(self, peer_id, info): """Import a peer from a transfer record or other source.""" if not peer_id: # A transfer's sender or recipient is not yet known. # There's nothing to import. return if not self.write_enabled: # This method doesn't need to do anything when writing is # disabled. return if peer_id == self.owner_id: # Get better info from the owner profile. info = { 'title': self.owner.title, 'screen_name': self.owner.username, 'is_dfi_account': False, 'is_own_dfi_account': False, } else: # Is the peer an account held by the user? If so, get # better info from the account map. account = self.account_map.get(peer_id) if account: title = '%s at %s' % ( account['redacted_account_num'], account['rdfi_name'], ) if account['alias']: title += ' (%s)' % account['alias'] info = { 'title': title, 'screen_name': '', 'is_dfi_account': True, 'is_own_dfi_account': True, } dbsession = self.request.dbsession peer = self.peers.get(peer_id) if peer is None: peer = Peer( owner_id=self.owner_id, peer_id=peer_id, title=info.get('title'), username=info.get('screen_name'), is_dfi_account=info.get('is_dfi_account'), is_own_dfi_account=info.get('is_own_dfi_account'), last_update=now_func, ) dbsession.add(peer) self.change_log.append({ 'event_type': 'peer_add', 'peer_id': peer_id, }) self.peers[peer_id] = peer dbsession.add(OwnerLog( owner_id=self.owner_id, personal_id=self.request.personal_id, event_type='peer_add', content={ 'peer_id': peer_id, 'info': info, })) else: attrs_found = 0 changes = {} # Changeable attrs attrs = ( ('title', 'title'), ('screen_name', 'username'), ) for source_attr, dest_attr in attrs: value = info.get(source_attr) if value: attrs_found += 1 if getattr(peer, dest_attr) != value: changes[dest_attr] = value setattr(peer, dest_attr, value) # One-shot boolean attrs (once set, stay set) attrs = ( ('is_dfi_account', 'is_dfi_account'), ('is_own_dfi_account', 'is_own_dfi_account'), ) for source_attr, dest_attr in attrs: value = info.get(source_attr) if value is not None: attrs_found += 1 if value and not getattr(peer, dest_attr): changes[dest_attr] = True setattr(peer, dest_attr, True) if attrs_found: peer.last_update = now_func if changes: self.change_log.append({ 'event_type': 'peer_update', 'peer_id': peer_id, }) dbsession.add(OwnerLog( owner_id=self.owner_id, personal_id=self.request.personal_id, event_type='peer_update', content={ 'peer_id': peer_id, 'changes': changes, }))
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)
def pull_unreco(request, period, op): """Pull unreconciled items from other open periods into this period. """ dbsession = request.dbsession owner = request.owner owner_id = owner.id assert period.owner_id == owner_id item_filter = and_( op.table.owner_id == owner_id, op.table.file_id == period.file_id, op.table.period_id != period.id, op.table.reco_id == null, ~Period.closed, ) # List the dates of all unreconciled items in other open periods # for the same peer loop. day_rows = (dbsession.query(op.date_c).join( Period, Period.id == op.table.period_id).filter(item_filter).distinct().all()) if not day_rows: # There are no items to pull in. return 0 # List the dates of the items to pull in. reassign_days = [] period_list = [period] for (day, ) in day_rows: if get_period_for_day(period_list, day, default_endless=False) is period: reassign_days.append(day) if not reassign_days: # None of the items found should be pulled in to this period. return 0 # Map the reassignable items to this period. day_periods, day_period_cte, missing_period = make_day_period_cte( days=sorted(reassign_days), period_list=period_list, default_endless=False) # Make a subquery that lists the items to reassign. ids_query = (select([op.id_c]).select_from( op.table.__table__.join(Period, Period.id == op.table.period_id).join( day_period_cte, day_period_cte.c.day == op.date_c)).where(item_filter)) item_ids = [item_id for (item_id, ) in dbsession.execute(ids_query)] # Reassign items. (dbsession.query(op.table).filter( op.id_c.in_(ids_query), op.table.file_id == period.file_id, ).update({'period_id': period.id}, synchronize_session='fetch')) dbsession.add( OwnerLog(owner_id=owner_id, personal_id=request.personal_id, event_type='pull_unreco_%s' % op.plural, content={ 'period_id': period.id, 'file_id': period.file_id, 'item_ids': item_ids, 'day_periods': day_periods, })) return len(item_ids)