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 get(self, disbursement_id): accounts = db.session.query(TransferAccount)\ .filter(TransferAccount.disbursements.any(id=disbursement_id))\ .options(joinedload(TransferAccount.disbursements)) accounts, total_items, total_pages, new_last_fetched = paginate_query( accounts) if accounts is None: response_object = { 'message': 'No transfer accounts', } return make_response(jsonify(response_object)), 400 transfer_accounts = transfer_accounts_schema.dump(accounts).data d = db.session.query(Disbursement).filter_by( id=disbursement_id).first() disbursement = disbursement_schema.dump(d).data response_object = { 'status': 'success', 'message': 'Successfully Loaded.', 'data': { 'transfer_accounts': transfer_accounts, 'disbursement': disbursement } } return make_response(jsonify(response_object)), 200
def put(self, transfer_account_id): put_data = request.get_json() transfer_account_id_list = put_data.get('transfer_account_id_list') approve = put_data.get('approve') transfer_account_name = put_data.get('transfer_account_name') payable_period_type = put_data.get('payable_period_type') payable_period_length = put_data.get('payable_period_length') payable_epoch = put_data.get('payable_epoch') if transfer_account_id: transfer_account = TransferAccount.query.get(transfer_account_id) if not transfer_account: response_object = {'message': 'Transfer account not found'} return make_response(jsonify(response_object)), 400 if transfer_account_name and not transfer_account_name == transfer_account.name: transfer_account.name = transfer_account_name if payable_period_type and not payable_period_type == transfer_account.payable_period_type: transfer_account.payable_period_type = payable_period_type if payable_period_length and not payable_period_length == transfer_account.payable_period_length: transfer_account.payable_period_length = payable_period_length if payable_epoch and not payable_epoch == transfer_account.payable_epoch: transfer_account.payable_epoch = payable_epoch if not approve == transfer_account.is_approved and transfer_account.is_approved is not True: transfer_account.approve() db.session.commit() result = transfer_account_schema.dump(transfer_account) response_object = { 'message': 'Successfully Edited Transfer Account.', 'data': { 'transfer_account': result.data, } } return make_response(jsonify(response_object)), 201 else: transfer_accounts = [] response_list = [] for transfer_account_id in transfer_account_id_list: transfer_account = TransferAccount.query.get( transfer_account_id) if not transfer_account: response_list.append({ 'status': 400, 'message': 'Transfer account id {} not found'.format( transfer_account_id) }) continue if not transfer_account.is_approved and approve: transfer_account.approve() transfer_accounts.append(transfer_account) db.session.commit() response_object = { 'status': 'success', 'message': 'Successfully Edited Transfer Accounts.', 'data': { 'transfer_accounts': transfer_accounts_schema.dump(transfer_accounts).data } } return make_response(jsonify(response_object)), 201
def get(self, transfer_account_id): # can_see_full_details = role in ['is_admin', 'is_view'] # # if not (can_see_full_details): # return less_detail 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 g.user.is_admin: result = transfer_account_schema.dump(transfer_account) elif g.user.is_view: 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: if account_type_filter == 'vendor': transfer_accounts_query = TransferAccount.query.filter_by( is_vendor=True) elif account_type_filter == 'beneficiary': transfer_accounts_query = TransferAccount.query.filter_by( is_vendor=False) else: transfer_accounts_query = TransferAccount.query transfer_accounts, total_items, total_pages = paginate_query( transfer_accounts_query, TransferAccount) if transfer_accounts is None: response_object = { 'message': 'No transfer accounts', } return make_response(jsonify(response_object)), 400 if g.user.is_admin: result = transfer_accounts_schema.dump(transfer_accounts) elif g.user.is_view: result = view_transfer_accounts_schema.dump(transfer_accounts) response_object = { 'message': 'Successfully Loaded.', 'items': total_items, 'pages': total_pages, 'data': { 'transfer_accounts': result.data } } return make_response(jsonify(response_object)), 201
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, 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, 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: base_query = TransferAccount.query.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)