def get(self): # HANDLE PARAM : search_stirng - Any search string. An empty string (or None) will just return everything! search_string = request.args.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : order # Valid orders types are: `ASC` and `DESC` # Default: DESC order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''.format(order_arg)} order = asc if order_arg.upper()=='ASC' else desc # HANDLE PARAM: sort_by # Valid orders types are: first_name, last_name, email, date_account_created, rank, balance, status # Default: rank sort_by_arg = request.args.get('sort_by') or 'rank' final_query = generate_search_query(search_string, filters, order, sort_by_arg) transfer_accounts, total_items, total_pages, _ = paginate_query(final_query, ignore_last_fetched=True) if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = transfer_accounts_schema.dump(transfer_accounts) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_transfer_accounts_schema.dump(transfer_accounts) return { 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'query_time': datetime.datetime.utcnow(), 'data': { 'transfer_accounts': result.data } }
def put(self): put_data = request.get_json() # HANDLE PARAM : search_stirng - Any search string. An empty string (or None) will just return everything! search_string = put_data.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = put_data.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : include_accounts - Explicitly include these users include_accounts = put_data.get('include_accounts', []) # HANDLE PARAM : include_accounts - Explicitly exclude these users exclude_accounts = put_data.get('exclude_accounts', []) approve = put_data.get('approve') if include_accounts and exclude_accounts: return { 'message': 'Please either include or exclude users (include is additive from the whole search, while exclude is subtractive)' } order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''. format(order_arg) } order = asc if order_arg.upper() == 'ASC' else desc sort_by_arg = request.args.get('sort_by') or 'rank' if include_accounts: transfer_accounts = db.session.query(TransferAccount).filter( TransferAccount.id.in_(include_accounts)).all() else: search_query = generate_search_query(search_string, filters, order, sort_by_arg, include_user=True) search_query = search_query.filter( TransferAccount.id.notin_(exclude_accounts)) results = search_query.all() transfer_accounts = [ r[0] for r in results ] # Get TransferAccount (TransferAccount, searchRank, User) for ta in transfer_accounts: if approve == True: ta.is_approved = True elif approve == False: ta.is_approved = False db.session.commit() response_object = { 'status': 'success', 'message': 'Successfully Edited Transfer Accounts.', } return make_response(jsonify(response_object)), 201
def get(self): start_date = request.args.get('start_date') end_date = request.args.get('end_date') encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) transfer_stats = calculate_transfer_stats(total_time_series=True, start_date=start_date, end_date=end_date, user_filter=filters) transfer_stats = { 'total_distributed': '15300.0000000000000000', 'total_spent': 0, 'total_exchanged': 0, 'has_transferred_count': 0, 'zero_balance_count': 0, 'total_beneficiaries': 3, 'total_users': 3, 'master_wallet_balance': 0, 'daily_transaction_volume': [{ 'date': '2020-04-29T07:05:01.771621', 'volume': 0 }], 'daily_disbursement_volume': [{ 'date': '2020-04-29T00:00:00', 'volume': ('300.0000000000000000') }, { 'date': '2020-04-28T00:00:00', 'volume': '15000.0000000000000000' }], 'transfer_use_breakdown': [], 'last_day_volume': { 'date': '2020-04-29T07:05:01.771617', 'volume': 0 }, 'filter_active': False } response_object = { 'status': 'success', 'message': 'Successfully Loaded.', 'data': { 'transfer_stats': transfer_stats } } return make_response(jsonify(response_object)), 200
def get(self): start_date = request.args.get('start_date') end_date = request.args.get('end_date') encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) transfer_stats = calculate_transfer_stats(total_time_series=True, start_date=start_date, end_date=end_date, user_filter=filters) response_object = { 'status': 'success', 'message': 'Successfully Loaded.', 'data': { 'transfer_stats': transfer_stats } } return make_response(jsonify(response_object)), 200
def get(self): # HANDLE PARAM : search_stirng - Any search string. An empty string (or None) will just return everything! search_string = request.args.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : order # Valid orders types are: `ASC` and `DESC` # Default: DESC order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''. format(order_arg) } order = asc if order_arg.upper() == 'ASC' else desc # HANDLE PARAM: sort_by # Valid orders types are: first_name, last_name, email, date_account_created, rank, balance, status # Default: rank sort_types_to_database_types = { 'first_name': User.first_name, 'last_name': User.last_name, 'email': User.email, 'date_account_created': User.created, 'rank': 'rank', 'balance': TransferAccount._balance_wei, 'status': TransferAccount.is_approved, } sort_by_arg = request.args.get('sort_by') or 'rank' if sort_by_arg not in sort_types_to_database_types: return { 'message': f'Invalid sort_by value {sort_by_arg}. Please use one of the following: {sort_types_to_database_types.keys()}'\ } # To add new searchable column, simply add a new SearchableColumn object! # And don't forget to add a trigram index on that column too-- see migration 33df5e72fca4 for reference user_search_columns = [ SearchableColumn('first_name', User.first_name, rank=1.5), SearchableColumn('last_name', User.last_name, rank=1.5), SearchableColumn('phone', User.phone, rank=2), SearchableColumn('public_serial_number', User.public_serial_number, rank=2), SearchableColumn('location', User.location, rank=1), SearchableColumn('primary_blockchain_address', User.primary_blockchain_address, rank=2), ] sum_search = reduce(lambda x, y: x + y, [ sc.get_similarity_query(search_string) for sc in user_search_columns ]) sort_by = sum_search if sort_by_arg == 'rank' else sort_types_to_database_types[ sort_by_arg] # If there's no search string, the process is the same, just sort by account creation date sort_by = sort_types_to_database_types[ 'date_account_created'] if sort_by == 'rank' and not search_string else sort_by final_query = db.session.query(TransferAccount, User, sum_search)\ .with_entities(TransferAccount, sum_search)\ .outerjoin(TransferAccount, User.default_transfer_account_id == TransferAccount.id)\ .filter(TransferAccount.is_ghost != True)\ .order_by(order(sort_by)) # If there is a search string, we only want to return ranked results! final_query = final_query.filter( sum_search != 0) if search_string else final_query final_query = apply_filters(final_query, filters, User) transfer_accounts, total_items, total_pages, _ = paginate_query( final_query, ignore_last_fetched=True) accounts = [resultTuple[0] for resultTuple in transfer_accounts] if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = transfer_accounts_schema.dump(accounts) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_transfer_accounts_schema.dump(accounts) return { 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'query_time': datetime.datetime.utcnow(), 'data': { 'transfer_accounts': result.data } }
def get(self): """ This endpoint searches transfer accounts and credit transfers. It will check first name/last name/phone number/email address Parameters: - search_string: Any string you want to search. When empty or not provided, all results will be returned. - search_type: Valid inputs are transfer_accounts, and credit_transfers. - order: Which order to return results in (ASC or DESC) - sort_by: What to sort by. `rank` works for both, and sorts by search relevance. - Transfer Accounts can be sorted by: 'first_name', 'last_name', 'email', 'date_account_created', 'rank', 'balance', 'status' - Credit Transfers can be sorted by: 'sender_first_name', 'sender_last_name', 'sender_email', 'sender_date_account_created', 'recipient_first_name', 'recipient_last_name', 'recipient_email', 'recipient_date_account_created', 'rank', 'amount', 'transfer_type', 'approval', 'date_transaction_created' Return Value: Results object, similar to the existing transfer_accounts and credit_transfers API return values """ # HANDLE PARAM : search_stirng search_string = request.args.get('search_string') or '' # HANDLE PARAM : search_type # Valid search types are: `transfer_accounts` and `credit_transfers` # Default: transfer_accounts search_type = request.args.get('search_type') or 'transfer_accounts' if search_type not in ['transfer_accounts', 'credit_transfers']: response_object = { 'message': 'Invalid search_type \'{}\'. Please use type \'transfer_accounts\' or \'credit_transfers\'' .format(search_type), } return make_response(jsonify(response_object)), 400 # HANDLE PARAM : sort_by # Valid params differ depending on sort_by. See: sort_by # Default: rank # Aliases used for joining the separate sender and recipient objects to transfers sender = aliased(User) recipient = aliased(User) # Build order by object sort_types_to_database_types = { 'first_name': User.first_name, 'last_name': User.last_name, 'email': User.email, 'date_account_created': User.created, 'rank': 'rank', 'balance': TransferAccount._balance_wei, 'status': TransferAccount.is_approved, 'amount': CreditTransfer.transfer_amount, 'transfer_type': CreditTransfer.transfer_type, 'approval': CreditTransfer.transfer_status, 'date_transaction_created': CreditTransfer.resolved_date, 'sender_first_name': sender.first_name, 'sender_last_name': sender.last_name, 'sender_email': sender.email, 'sender_date_account_created': recipient.created, 'recipient_first_name': recipient.first_name, 'recipient_last_name': recipient.last_name, 'recipient_email': recipient.email, 'recipient_date_account_created': recipient.created } # These lists are to validate the user input-- not using sort_types_to_database_types since credit_transfers and transfer_accounts have unique options user_sorting_options = [ 'first_name', 'last_name', 'email', 'date_account_created' ] sender_sorting_options = list( map(lambda s: 'sender_' + s, user_sorting_options) ) # sender_first_name, sender_last_name, etc... recipient_sorting_options = list( map(lambda s: 'recipient_' + s, user_sorting_options) ) # recipient_first_name, recipient_last_name, etc... sorting_options = { 'transfer_accounts': [*user_sorting_options, 'rank', 'balance', 'status'], 'credit_transfers': [ *sender_sorting_options, *recipient_sorting_options, 'rank', 'amount', 'transfer_type', 'approval', 'date_transaction_created' ] } sort_by_arg = request.args.get('sort_by') or 'rank' if sort_by_arg not in sorting_options[search_type]: response_object = { # Example output: # "Invalid sort_by value 'pizza'. Please use one of the following: 'first_name', 'last_name', 'email', 'rank', 'balance', 'status', 'date_account_created'" 'message': 'Invalid sort_by value \'{}\'. Please use one of the following: {}'\ .format(sort_by_arg, ', '.join('\'{}\''.format(a) for a in sorting_options[search_type])), } return make_response(jsonify(response_object)), 400 sort_by = sort_types_to_database_types[sort_by_arg] encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : order # Valid orders types are: `ASC` and `DESC` # Default: DESC order_arg = request.args.get('order') or 'DESC' if order_arg not in ['ASC', 'DESC']: response_object = { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''. format(order_arg), } return make_response(jsonify(response_object)), 400 order = desc if order_arg == 'ASC': order = asc # Note: Using tsquery wildcards here. Good docs of them here: # https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY # 'Fran deRoo' -> 'Fran:* | deRoo:*' # Matches strings like "Francine deRoos" # Will also match "Michiel deRoos" because of the or clause, but this will be ranked lower search_string = re.sub('\s+', ' ', search_string) search_terms = search_string.strip().split(' ') tsquery = ':* | '.join(search_terms) + ':*' # Return everything if the search string is empty if search_string == '': if search_type == 'transfer_accounts': final_query = TransferAccount.query.filter(TransferAccount.is_ghost != True)\ .join(User, User.default_transfer_account_id == TransferAccount.id) final_query = apply_filters(final_query, filters, User) if sort_by_arg == 'rank': # There's no search rank when there's no query string, so do chrono instead final_query = final_query.order_by(order(User.created)) else: final_query = final_query.order_by(order(sort_by)) transfer_accounts, total_items, total_pages = paginate_query( final_query, TransferAccount) result = transfer_accounts_schema.dump(transfer_accounts) data = {'transfer_accounts': result.data} else: final_query = CreditTransfer.query.filter()\ .outerjoin(sender, sender.default_transfer_account_id == CreditTransfer.sender_transfer_account_id)\ .outerjoin(recipient, recipient.default_transfer_account_id == CreditTransfer.recipient_transfer_account_id) if sort_by_arg == 'rank': # There's no search rank when there's no query string, so do chrono instead final_query = final_query.order_by( order(CreditTransfer.created)) else: final_query = final_query.order_by(order(sort_by)) credit_transfers, total_items, total_pages = paginate_query( final_query, CreditTransfer) result = credit_transfers_schema.dump(credit_transfers) data = {'credit_transfers': result.data} else: # First get users who match search string user_search_result = db.session.query( db.distinct(SearchView.id), SearchView, # This ugly (but functional) multi-tscolumn ranking is a modified from Ben Smithgall's blog post # https://www.codeforamerica.org/blog/2015/07/02/multi-table-full-text-search-with-postgres-flask-and-sqlalchemy/ db.func.max(db.func.full_text.ts_rank( db.func.setweight(db.func.coalesce(SearchView.tsv_email, ''), 'D')\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_phone, ''), 'A'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_first_name, ''), 'B'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_last_name, ''), 'B'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_public_serial_number, ''), 'A'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_primary_blockchain_address, ''), 'A'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_location, ''), 'C'))\ .concat(db.func.setweight(db.func.coalesce(SearchView.tsv_default_transfer_account_id, ''), 'A')), db.func.to_tsquery(tsquery, postgresql_regconfig='english')))\ .label('rank'))\ .group_by(SearchView)\ .subquery() # Then use those results to join aginst TransferAccount or CreditTransfer if search_type == 'transfer_accounts': # TransferAccount Search Logic final_query = db.session.query(TransferAccount)\ .join(user_search_result, user_search_result.c.default_transfer_account_id == TransferAccount.id)\ .join(User, user_search_result.c.default_transfer_account_id == User.default_transfer_account_id)\ .filter(user_search_result.c.rank > 0.0)\ .filter(TransferAccount.is_ghost != True) final_query = apply_filters(final_query, filters, User) if sort_by_arg == 'rank': final_query = final_query.order_by( order(user_search_result.c.rank)) else: final_query = final_query.order_by(order(sort_by)) transfer_accounts, total_items, total_pages = paginate_query( final_query, TransferAccount) result = transfer_accounts_schema.dump(transfer_accounts) data = {'transfer_accounts': result.data} # CreditTransfer Search Logic else: sender_search_result = aliased(user_search_result) recipient_search_result = aliased(user_search_result) # Join the search results objects to sort by rank, as well as aliased user objects (sender/recipient) for other sorting options final_query = db.session.query(CreditTransfer)\ .outerjoin(sender_search_result, sender_search_result.c.default_transfer_account_id == CreditTransfer.sender_transfer_account_id )\ .outerjoin(recipient_search_result, recipient_search_result.c.default_transfer_account_id == CreditTransfer.recipient_transfer_account_id )\ .outerjoin(sender, sender_search_result.c.default_transfer_account_id == sender.default_transfer_account_id )\ .outerjoin(recipient, recipient_search_result.c.default_transfer_account_id == recipient.default_transfer_account_id )\ .filter(or_(recipient_search_result.c.rank > 0.0, sender_search_result.c.rank > 0.0)) if sort_by_arg == 'rank': final_query = final_query.order_by( order(recipient_search_result.c.rank + sender_search_result.c.rank)) else: final_query = final_query.order_by(order(sort_by)) credit_transfers, total_items, total_pages = paginate_query( final_query, CreditTransfer) result = credit_transfers_schema.dump(credit_transfers) data = {'credit_transfers': result.data} response_object = { 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'query_time': datetime.datetime.utcnow(), 'data': data } bytes_data = orjson.dumps(response_object) resp = make_response(bytes_data, 200) resp.mimetype = 'application/json' return resp
def get(self, credit_transfer_id): transfer_account_ids = request.args.get('transfer_account_ids') # Handle search parameters # HANDLE PARAM : search_string - Any search string. An empty string (or None) will just return everything! search_string = request.args.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : order # Valid orders types are: `ASC` and `DESC` # Default: DESC order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''. format(order_arg) } order = asc if order_arg.upper() == 'ASC' else desc # HANDLE PARAM: sort_by # Valid orders types are: first_name, last_name, email, date_account_created, rank, balance, status # Default: rank sort_by_arg = request.args.get('sort_by') or 'rank' if credit_transfer_id: credit_transfer = CreditTransfer.query.get(credit_transfer_id) if credit_transfer is None: return make_response( jsonify({'message': 'Credit transfer not found'})), 404 if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = credit_transfer_schema.dump(credit_transfer).data elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_credit_transfer_schema.dump(credit_transfer).data transfer_stats = [] response_object = { 'status': 'success', 'message': 'Successfully Loaded.', 'data': { 'credit_transfer': result, 'transfer_stats': transfer_stats } } return make_response(jsonify(response_object)), 200 else: if transfer_account_ids: # We're getting a list of transfer accounts - parse try: parsed_transfer_account_ids = list( map(lambda x: int(x), filter(None, transfer_account_ids.split(',')))) except Exception as e: response_object = {'status': 'fail', 'message': str(e)} return make_response(jsonify(response_object)), 400 if parsed_transfer_account_ids: try: query = generate_search_query( search_string, filters, order, sort_by_arg, search_type=CREDIT_TRANSFER) except Exception as e: response_object = {'status': 'fail', 'message': str(e)} return make_response(jsonify(response_object)), 200 final_query = query.filter( or_( CreditTransfer.recipient_transfer_account_id.in_( parsed_transfer_account_ids), CreditTransfer.sender_transfer_account_id.in_( parsed_transfer_account_ids))) credit_transfers, total_items, total_pages, new_last_fetched = paginate_query( final_query, ignore_last_fetched=True) if AccessControl.has_sufficient_tier( g.user.roles, 'ADMIN', 'admin'): result = credit_transfers_schema.dump(credit_transfers) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_credit_transfers_schema.dump( credit_transfers) return { 'status': 'success', 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'last_fetched': new_last_fetched, 'query_time': datetime.datetime.utcnow(), 'data': { 'credit_transfers': result.data } } else: try: final_query = generate_search_query( search_string, filters, order, sort_by_arg, search_type=CREDIT_TRANSFER) except Exception as e: response_object = {'status': 'fail', 'message': str(e)} return make_response(jsonify(response_object)), 200 credit_transfers, total_items, total_pages, new_last_fetched = paginate_query( final_query, ignore_last_fetched=True) if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = credit_transfers_schema.dump(credit_transfers) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_credit_transfers_schema.dump(credit_transfers) return { 'status': 'success', 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'last_fetched': new_last_fetched, 'query_time': datetime.datetime.utcnow(), 'data': { 'credit_transfers': result.data } }
def get(self, transfer_account_id): account_type_filter = request.args.get('account_type') result = None if transfer_account_id: transfer_account = TransferAccount.query.get(transfer_account_id) if transfer_account is None: response_object = { 'message': 'No such transfer account: {}'.format(transfer_account_id), } return make_response(jsonify(response_object)), 400 if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = transfer_account_schema.dump(transfer_account) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_transfer_account_schema.dump(transfer_account) response_object = { 'message': 'Successfully Loaded.', 'data': { 'transfer_account': result.data, } } return make_response(jsonify(response_object)), 201 else: search_string = request.args.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = request.args.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : order # Valid orders types are: `ASC` and `DESC` # Default: DESC order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\'' .format(order_arg) } order = asc if order_arg.upper() == 'ASC' else desc # HANDLE PARAM: sort_by # Valid orders types are: first_name, last_name, email, date_account_created, rank, balance, status # Default: rank sort_by_arg = request.args.get('sort_by') or 'rank' base_query = generate_search_query( search_string, filters, order, sort_by_arg).filter(TransferAccount.is_ghost != True) if account_type_filter == 'vendor': transfer_accounts_query = base_query.filter_by(is_vendor=True) elif account_type_filter == 'beneficiary': transfer_accounts_query = base_query.filter_by( is_beneficiary=True) else: pass # Filter Contract, Float and Organisation Transfer Accounts transfer_accounts_query = (base_query.filter( TransferAccount.account_type == TransferAccountType.USER)) transfer_accounts, total_items, total_pages, new_last_fetched = paginate_query( transfer_accounts_query) if transfer_accounts is None: response_object = { 'message': 'No transfer accounts', } return make_response(jsonify(response_object)), 400 if AccessControl.has_sufficient_tier(g.user.roles, 'ADMIN', 'admin'): result = transfer_accounts_schema.dump(transfer_accounts) elif AccessControl.has_any_tier(g.user.roles, 'ADMIN'): result = view_transfer_accounts_schema.dump(transfer_accounts) response_object = { 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'last_fetched': new_last_fetched, 'query_time': datetime.datetime.utcnow().strftime("%m/%d/%Y, %H:%M:%S"), 'data': { 'transfer_accounts': result.data } } return make_response(json.dumps(response_object), 200)
def get(self): """ This endpoint generates metrics for both transfers and participants. By default, it returns all metric types, but can provide a certian type of metric with the metric_type parameter. When requesting `transfer` metric types, all `encoded_filters` are applicable, but only a subset are applicable to `participant` metrics. To see which metrics are applicable to which metric type, see documentation for the `/metrics/filters/` endpoint. Parameters: - start_date: (Default: None) Start date string of range to query. Format: "2020-01-01T15:00:00.000Z" - end_date: (Default: None) End date string of range to query. Format: "2020-01-01T15:00:00.000Z" - encoded_filters: (Default: None) Set of filters to apply to the metrics query. Additional documentation for filters can be found in /utils/transfer_filter.py - disable_cache: (Default: False) Force-disables cache - metric_type: (Default: 'all') Allows the user to swtich between `transfer`, `participant`, and `all` - timeseries_unit: (Default: 'day') Allows the user to swtich between `day`, `week`, `month` and `year` - group_by: (Default: 'ungrouped') Allows the user to swtich choose group_by category. See /metrics/filters for all options - token_id: (Default: None) If multi-org is being used, and the orgs have different tokens, this lets the user choose which token's stats to present """ start_date = request.args.get('start_date') end_date = request.args.get('end_date') encoded_filters = request.args.get('params') disable_cache = request.args.get('disable_cache', 'False').lower() in [ 'true', '1' ] # Defaults to bool false metric_type = request.args.get('metric_type', metrics_const.ALL) requested_metric = request.args.get('requested_metric', metrics_const.ALL) timeseries_unit = request.args.get('timeseries_unit', metrics_const.DAY) group_by = request.args.get('group_by', metrics_const.UNGROUPED) token_id = request.args.get('token_id', None) if timeseries_unit not in metrics_const.TIMESERIES_UNITS: raise Exception( f'{timeseries_unit} not a valid timeseries unit. Please choose one of the following: {", ".join(metrics_const.TIMESERIES_UNITS)}' ) if metric_type not in metrics_const.METRIC_TYPES: raise Exception( f'{metric_type} not a valid metric type. Please choose one of the following: {", ".join(metrics_const.METRIC_TYPES)}' ) groups = Groups() if group_by not in groups.GROUP_TYPES.keys(): raise Exception( f'{group_by} not a valid grouping type. Please choose one of the following: {", ".join(groups.GROUP_TYPES.keys())}' ) filters = process_transfer_filters(encoded_filters) transfer_stats = calculate_transfer_stats( start_date=start_date, end_date=end_date, user_filter=filters, metric_type=metric_type, requested_metric=requested_metric, disable_cache=disable_cache, timeseries_unit=timeseries_unit, group_by=group_by, token_id=token_id) response_object = { 'status': 'success', 'message': 'Successfully Loaded.', 'data': { 'transfer_stats': transfer_stats } } return make_response(jsonify(response_object)), 200
def post(self): post_data = request.get_json() # --- Handle Parameters --- # HANDLE PARAM : label - Name for the disbursement label = post_data.get('label') or '' # HANDLE PARAM : search_string - Any search string. An empty string (or None) will just return everything! search_string = post_data.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = post_data.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : include_accounts - Explicitly include these users include_accounts = post_data.get('include_accounts', []) # HANDLE PARAM : include_accounts - Explicitly exclude these users exclude_accounts = post_data.get('exclude_accounts', []) disbursement_amount = abs( round(Decimal(post_data.get('disbursement_amount') or 0), 6)) if include_accounts and exclude_accounts: return { 'message': 'Please either include or exclude users (include is additive from the whole search, while exclude is subtractive)' } # HANDLE PARAM : transfer_type - Transfer type-- either DISBURSEMENT, RECLAMATION, or BALANCE transfer_type = post_data.get('transfer_type', 'DISBURSEMENT') if transfer_type not in ['DISBURSEMENT', 'RECLAMATION', 'BALANCE']: return { 'message': f'{transfer_type} not a valid transfer type. Please choose one of DISBURSEMENT, RECLAMATION, or BALANCE' } order_arg = request.args.get('order') or 'DESC' if order_arg.upper() not in ['ASC', 'DESC']: return { 'message': 'Invalid order value \'{}\'. Please use \'ASC\' or \'DESC\''. format(order_arg) } order = asc if order_arg.upper() == 'ASC' else desc sort_by_arg = request.args.get('sort_by') or 'rank' # --- Build Disbursement Object --- d = Disbursement(creator_user=g.user, label=label, search_string=search_string, search_filter_params=encoded_filters, include_accounts=include_accounts, exclude_accounts=exclude_accounts, disbursement_amount=disbursement_amount, transfer_type=transfer_type) if include_accounts: transfer_accounts = db.session.query(TransferAccount).filter( TransferAccount.id.in_(include_accounts)).all() else: search_query = generate_search_query(search_string, filters, order, sort_by_arg, include_user=True) search_query = search_query.filter( TransferAccount.id.notin_(exclude_accounts)) results = search_query.all() transfer_accounts = [ r[0] for r in results ] # Get TransferAccount (TransferAccount, searchRank, User) d.transfer_accounts.extend(transfer_accounts) db.session.flush() disbursement = disbursement_schema.dump(d).data response_object = { 'data': { 'status': 'success', 'disbursement': disbursement } } return make_response(jsonify(response_object)), 201
def generate_export(post_data): from server.models.credit_transfer import CreditTransfer from server.models.transfer_account import TransferAccount from server.models.user import User export_type = post_data.get('export_type') include_transfers = post_data.get('include_transfers') # True or False include_custom_attributes = post_data.get( 'include_custom_attributes') # True or False user_type = post_data.get('user_type') # Beneficiaries, Vendors, All # TODO: implement date_range date_range = post_data.get('date_range') # last day, previous week, or all payable_period_type = post_data.get('payable_period_type', 'month') # day, week, month payable_period_length = post_data.get( 'payable_period_length', 1) # Integer, ie "payable every _2_ months" payable_epoch = post_data.get( 'payable_epoch') # When did the very first cycle begin? payable_period_start_date = post_data.get( 'payable_period_start_date' ) # any sort of reasonable date string for custom date period payable_period_end_date = post_data.get('payable_period_end_date') transfer_account_columns = [ { 'header': 'Account ID', 'query_type': 'db', 'query': 'id' }, { 'header': 'User ID', 'query_type': 'custom', 'query': 'user_id' }, { 'header': 'First Name', 'query_type': 'custom', 'query': 'first_name' }, { 'header': 'Last Name', 'query_type': 'custom', 'query': 'last_name' }, { 'header': 'Public Serial Number', 'query_type': 'custom', 'query': 'public_serial_number' }, { 'header': 'Phone', 'query_type': 'custom', 'query': 'phone' }, { 'header': 'Created (UTC)', 'query_type': 'db', 'query': 'created' }, { 'header': 'Approved', 'query_type': 'db', 'query': 'is_approved' }, { 'header': 'Beneficiary', 'query_type': 'custom', 'query': 'has_beneficiary_role' }, { 'header': 'Vendor', 'query_type': 'custom', 'query': 'has_vendor_role' }, { 'header': 'Location', 'query_type': 'custom', 'query': 'location' }, { 'header': 'Current Balance', 'query_type': 'custom', 'query': 'balance' }, { 'header': 'Amount Received', 'query_type': 'custom', 'query': 'received' }, { 'header': 'Amount Sent', 'query_type': 'custom', 'query': 'sent' } # {'header': 'Prev. Period Payable', 'query_type': 'custom', 'query': 'prev_period_payable'}, # {'header': 'Total Payable', 'query_type': 'custom', 'query': 'total_payable'}, ] credit_transfer_columns = [ { 'header': 'ID', 'query_type': 'db', 'query': 'id' }, { 'header': 'Transfer Amount', 'query_type': 'custom', 'query': 'transfer_amount' }, { 'header': 'Created', 'query_type': 'db', 'query': 'created' }, { 'header': 'Resolved Date', 'query_type': 'db', 'query': 'resolved_date' }, { 'header': 'Transfer Type', 'query_type': 'enum', 'query': 'transfer_type' }, { 'header': 'Transfer Type', 'query_type': 'enum', 'query': 'transfer_subtype' }, { 'header': 'Transfer Status', 'query_type': 'enum', 'query': 'transfer_status' }, { 'header': 'Sender ID', 'query_type': 'db', 'query': 'sender_transfer_account_id' }, { 'header': 'Recipient ID', 'query_type': 'db', 'query': 'recipient_transfer_account_id' }, { 'header': 'Transfer Uses', 'query_type': 'custom', 'query': 'transfer_usages' }, ] # need to add Balance (Payable) random_string = ''.join(random.choices(string.ascii_letters, k=5)) # TODO MAKE THIS API AUTHED time = str(datetime.utcnow()) base_filename = current_app.config['DEPLOYMENT_NAME'] + '-id' + str( g.user.id) + '-' + str(time[0:10]) + '-' + random_string workbook_filename = base_filename + '.xlsx' # e.g. dev-id1-2018-09-19-asfi.xlsx pdf_filename = base_filename + '.pdf' wb = Workbook() ws = wb.new_sheet("transfer_accounts") # ws1 = wb.create_sheet(title='transfer_accounts') start_date = None end_date = None user_filter = None for index, column in enumerate(transfer_account_columns): ws[1][index + 1] = column['header'] # Create transfer_accounts workbook headers for index, column in enumerate(transfer_account_columns): ws[1][index + 1] = column['header'] user_accounts = [] credit_transfer_list = [] # filter user accounts if user_type == 'beneficiary': user_filter = User.has_beneficiary_role if user_type == 'vendor': user_filter = User.has_vendor_role if date_range == 'all': end_date = datetime.utcnow() start_date = end_date - timedelta(weeks=520) if date_range == 'day': # return previous day of transactions end_date = datetime.utcnow() start_date = end_date - timedelta(days=1) if date_range == 'week': # return previous week of transactions end_date = datetime.utcnow() start_date = end_date - timedelta(weeks=1) if user_filter is not None: user_accounts = User.query.filter(user_filter == True).options( joinedload(User.transfer_accounts)) user_accounts = partition_query(user_accounts) elif user_type == 'selected': # HANDLE PARAM : search_string - Any search string. An empty string (or None) will just return everything! search_string = post_data.get('search_string') or '' # HANDLE PARAM : params - Standard filter object. Exact same as the ones Metrics uses! encoded_filters = post_data.get('params') filters = process_transfer_filters(encoded_filters) # HANDLE PARAM : include_accounts - Explicitly include these users include_accounts = post_data.get('include_accounts', []) # HANDLE PARAM : include_accounts - Explicitly exclude these users exclude_accounts = post_data.get('exclude_accounts', []) if include_accounts: transfer_accounts = db.session.query( TransferAccount).options().filter( TransferAccount.id.in_(include_accounts)) transfer_accounts = partition_query(transfer_accounts) else: search_query = generate_search_query(search_string, filters, order=desc, sort_by_arg='rank', include_user=True) search_query = search_query.filter( TransferAccount.id.notin_(exclude_accounts)) results = partition_query(search_query) transfer_accounts = [ r[0] for r in results ] # Get TransferAccount (TransferAccount, searchRank, User) user_accounts = [ta.primary_user for ta in transfer_accounts] else: user_accounts = User.query.filter( or_(User.has_beneficiary_role == True, User.has_vendor_role == True)).options( joinedload(User.transfer_accounts)) user_accounts = partition_query(user_accounts) if export_type == 'pdf': file_url = generate_pdf_export(user_accounts, pdf_filename) response_object = { 'message': 'Export file created.', 'data': { 'file_url': file_url, } } return make_response(jsonify(response_object)), 201 if user_accounts is not None: custom_attribute_columns = [] for index, user_account in enumerate(user_accounts): transfer_account = user_account.transfer_account if transfer_account: for jindix, column in enumerate(transfer_account_columns): if column['query_type'] == 'db': cell_contents = "{0}".format( getattr(transfer_account, column['query'])) elif column['query'] == 'user_id': cell_contents = "{0}".format( transfer_account.primary_user.id) elif column['query'] == 'first_name': cell_contents = "{0}".format( transfer_account.primary_user.first_name) elif column['query'] == 'last_name': cell_contents = "{0}".format( transfer_account.primary_user.last_name) elif column['query'] == 'phone': cell_contents = "{0}".format( transfer_account.primary_user.phone or '') elif column['query'] == 'public_serial_number': cell_contents = "{0}".format( transfer_account.primary_user.public_serial_number or '') elif column['query'] == 'location': cell_contents = "{0}".format( transfer_account.primary_user._location) elif column['query'] == 'balance': cell_contents = getattr(transfer_account, column['query']) / 100 elif column['query'] == 'has_beneficiary_role': cell_contents = "{0}".format( transfer_account.primary_user.has_beneficiary_role) elif column['query'] == 'has_vendor_role': cell_contents = "{0}".format( transfer_account.primary_user.has_vendor_role) elif column['query'] == 'received': received_amount = transfer_account.total_received cell_contents = received_amount / 100 elif column['query'] == 'sent': sent_amount = transfer_account.total_sent cell_contents = sent_amount / 100 elif column['query'] == 'prev_period_payable': if payable_period_start_date and payable_period_end_date: prior_period_start = parser.parse( payable_period_start_date) prior_period_end = parser.parse( payable_period_end_date) else: if payable_epoch is None: payable_epoch = transfer_account.created else: payable_epoch = parser.parse( str(payable_epoch)) prior_period_start, prior_period_end = find_last_period_dates( payable_epoch, datetime.utcnow(), payable_period_type, payable_period_length) in_for_period = 0 # All funds start of last period, up to end of last period, NOT including after last period in_transactions = CreditTransfer.query.filter( and_( CreditTransfer.recipient_transfer_account_id == transfer_account.id, CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.resolved_date.between( prior_period_start, prior_period_end))) in_transactions = partition_query(in_transactions) for transaction in in_transactions: in_for_period += transaction.transfer_amount out_for_period = 0 # Out transactions DO NOT include reimbursements (withdrawals) from the previous month out_transactions = CreditTransfer.query.filter( and_( CreditTransfer.sender_transfer_account_id == transfer_account.id, CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.resolved_date.between( prior_period_start, prior_period_end), CreditTransfer.transfer_type != TransferTypeEnum.WITHDRAWAL)) out_transactions = partition_query(out_transactions) for transaction in out_transactions: out_for_period += transaction.transfer_amount payable_balance = in_for_period - out_for_period cell_contents = payable_balance / 100 elif column['query'] == 'total_payable': if payable_period_end_date: prior_period_end = parser.parse( payable_period_end_date) else: if payable_epoch is None: payable_epoch = transfer_account.created else: payable_epoch = parser.parse( str(payable_epoch)) prior_period_start, prior_period_end = find_last_period_dates( payable_epoch, datetime.utcnow(), payable_period_type, payable_period_length) in_for_period = 0 # All funds in from epoch, up to end of last period, NOT including after last period in_transactions = CreditTransfer.query.filter( and_( CreditTransfer.recipient_transfer_account_id == transfer_account.id, CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.resolved_date.between( payable_epoch, prior_period_end))) in_transactions = partition_query(in_transactions) for transaction in in_transactions: in_for_period += transaction.transfer_amount out_for_period = 0 # All funds out over all time, _including_ withdrawals out_transactions = CreditTransfer.query.filter( and_( CreditTransfer.sender_transfer_account_id == transfer_account.id, CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, CreditTransfer.resolved_date.between( payable_epoch, datetime.utcnow()))) out_transactions = partition_query(out_transactions) for transaction in out_transactions: out_for_period += transaction.transfer_amount payable_balance = in_for_period - out_for_period cell_contents = payable_balance / 100 else: cell_contents = "" ws[index + 2][jindix + 1] = cell_contents if include_custom_attributes: # Add custom attributes as columns at the end for attribute in transfer_account.primary_user.custom_attributes: name = (attribute.custom_attribute and attribute.custom_attribute.name) or ' ' try: col_num = custom_attribute_columns.index( name) + 1 + len(transfer_account_columns) except ValueError: custom_attribute_columns.append(name) col_num = len(custom_attribute_columns) + len( transfer_account_columns) ws[index + 2][col_num] = attribute.value else: print('No Transfer Account for user account id: ', user_account.id) # Add custom attribute headers: if include_custom_attributes: for index, column_name in enumerate(custom_attribute_columns): ws[1][index + 1 + len(transfer_account_columns)] = column_name if include_transfers and user_accounts is not None: base_credit_transfer_query = CreditTransfer.query.enable_eagerloads( False) if start_date and end_date is not None: credit_transfer_list = base_credit_transfer_query.filter( CreditTransfer.created.between(start_date, end_date)) if date_range == 'all': credit_transfer_list = base_credit_transfer_query credit_transfer_list = partition_query(credit_transfer_list) transfer_sheet = wb.new_sheet("credit_transfers") # Create credit_transfers workbook headers for index, column in enumerate(credit_transfer_columns): transfer_sheet[1][index + 1] = column['header'] if credit_transfer_list is not None: for index, credit_transfer in enumerate(credit_transfer_list): for jindix, column in enumerate(credit_transfer_columns): if column['query_type'] == 'db': cell_contents = "{0}".format( getattr(credit_transfer, column['query'])) elif column['query_type'] == 'enum': enum = getattr(credit_transfer, column['query']) cell_contents = "{0}".format(enum and enum.value) elif column['query'] == 'transfer_amount': cell_contents = "{0}".format( getattr(credit_transfer, column['query']) / 100) elif column['query'] == 'transfer_usages': cell_contents = ', '.join([ usage.name for usage in credit_transfer.transfer_usages ]) else: cell_contents = "" transfer_sheet[index + 2][jindix + 1] = cell_contents else: print('No Credit Transfers') file_url = export_workbook_via_s3(wb, workbook_filename) response_object = { 'message': 'Export file created.', 'data': { 'file_url': file_url, } } return make_response(jsonify(response_object)), 201