def _process_failed_payments(row):
"""Handle failed payments."""
# 1. Set the cfs_account status as FREEZE.
# 2. Call cfs api to Stop further PAD on this account.
# 3. Reverse the invoice_reference status to ACTIVE, invoice status to SETTLEMENT_SCHED, and delete receipt.
# 4. Create an NSF invoice for this account.
# 5. Create invoice reference for the newly created NSF invoice.
# 6. Adjust invoice in CFS to include NSF fees.
inv_number = _get_row_value(row, Column.TARGET_TXN_NO)
# Set CFS Account Status.
payment_account: PaymentAccountModel = _get_payment_account(row)
cfs_account: CfsAccountModel = CfsAccountModel.find_effective_by_account_id(
payment_account.id)
logger.info('setting payment account id : %s status as FREEZE',
payment_account.id)
cfs_account.status = CfsAccountStatus.FREEZE.value
# Call CFS to stop any further PAD transactions on this account.
CFSService.suspend_cfs_account(cfs_account)
# Find the invoice_reference for this invoice and mark it as ACTIVE.
inv_references: List[InvoiceReferenceModel] = db.session.query(InvoiceReferenceModel). \
filter(InvoiceReferenceModel.status_code == InvoiceReferenceStatus.COMPLETED.value). \
filter(InvoiceReferenceModel.invoice_number == inv_number). \
all()
# Update status to ACTIVE, if it was marked COMPLETED
for inv_reference in inv_references:
inv_reference.status_code = InvoiceReferenceStatus.ACTIVE.value
# Find receipt and delete it.
receipt: ReceiptModel = ReceiptModel.find_by_invoice_id_and_receipt_number(
invoice_id=inv_reference.invoice_id)
if receipt:
db.session.delete(receipt)
# Find invoice and update the status to SETTLEMENT_SCHED
invoice: InvoiceModel = InvoiceModel.find_by_id(
identifier=inv_reference.invoice_id)
invoice.invoice_status_code = InvoiceStatus.SETTLEMENT_SCHEDULED.value
invoice.paid = 0
# Create an invoice for NSF for this account
invoice = _create_nsf_invoice(cfs_account, inv_number, payment_account)
# Adjust CFS invoice
CFSService.add_nsf_adjustment(cfs_account=cfs_account,
inv_number=inv_number,
amount=invoice.total)
async def cb_subscription_handler(msg: nats.aio.client.Msg):
"""Use Callback to process Queue Msg objects."""
try:
logger.info('Received raw message seq:%s, data= %s', msg.sequence, msg.data.decode())
payment_token = extract_payment_token(msg)
logger.debug('Extracted payment token: %s', payment_token)
await process_payment(payment_token, FLASK_APP)
except OperationalError as err:
logger.error('Queue Blocked - Database Issue: %s', json.dumps(payment_token), exc_info=True)
raise err # We don't want to handle the error, as a DB down would drain the queue
except FilingException as err:
logger.error('Queue Error - cannot find filing: %s'
'\n\nThis message has been put back on the queue for reprocessing.',
json.dumps(payment_token), exc_info=True)
raise err # we don't want to handle the error, so that the message gets put back on the queue
except (QueueException, Exception): # pylint: disable=broad-except
# Catch Exception so that any error is still caught and the message is removed from the queue
capture_message('Queue Error:' + json.dumps(payment_token), level='error')
logger.error('Queue Error: %s', json.dumps(payment_token), exc_info=True)
def _sync_credit_records():
"""Sync credit records with CFS."""
# 1. Get all credit records with balance > 0
# 2. If it's on account receipt call receipt endpoint and calculate balance.
# 3. If it's credit memo, call credit memo endpoint and calculate balance.
# 4. Roll up the credits to credit field in payment_account.
active_credits: List[CreditModel] = db.session.query(CreditModel).filter(
CreditModel.remaining_amount > 0).all()
logger.info('Found %s credit records', len(active_credits))
account_ids: List[int] = []
for credit in active_credits:
account_ids.append(credit.account_id)
cfs_account: CfsAccountModel = CfsAccountModel.find_effective_by_account_id(
credit.account_id)
if credit.is_credit_memo:
credit_memo = CFSService.get_cms(cfs_account=cfs_account,
cms_number=credit.cfs_identifier)
credit.remaining_amount = abs(float(credit_memo.get('amount_due')))
else:
receipt = CFSService.get_receipt(
cfs_account=cfs_account, receipt_number=credit.cfs_identifier)
receipt_amount = float(receipt.get('receipt_amount'))
applied_amount: float = 0
for invoice in receipt.get('invoices', []):
applied_amount += float(invoice.get('amount_applied'))
credit.remaining_amount = receipt_amount - applied_amount
credit.save()
# Roll up the credits and add up to credit in payment_account.
for account_id in set(account_ids):
account_credits: List[CreditModel] = db.session.query(
CreditModel).filter(CreditModel.remaining_amount > 0).filter(
CreditModel.account_id == account_id).all()
credit_total: float = 0
for account_credit in account_credits:
credit_total += account_credit.remaining_amount
pay_account: PaymentAccountModel = PaymentAccountModel.find_by_id(
account_id)
pay_account.credit = credit_total
pay_account.save()
async def cb_subscription_handler(msg: nats.aio.client.Msg):
"""Use Callback to process Queue Msg objects."""
try:
logger.info('Received raw message seq: %s, data= %s', msg.sequence,
msg.data.decode())
email_msg = json.loads(msg.data.decode('utf-8'))
logger.debug('Extracted email msg: %s', email_msg)
process_email(email_msg, FLASK_APP)
except OperationalError as err:
logger.error('Queue Blocked - Database Issue: %s',
json.dumps(email_msg),
exc_info=True)
raise err # We don't want to handle the error, as a DB down would drain the queue
except EmailException as err:
logger.error(
'Queue Error - email failed to send: %s'
'\n\nThis message has been put back on the queue for reprocessing.',
json.dumps(email_msg),
exc_info=True)
raise err # we don't want to handle the error, so that the message gets put back on the queue
except (QueueException, Exception): # pylint: disable=broad-except
# Catch Exception so that any error is still caught and the message is removed from the queue
capture_message('Queue Error: ' + json.dumps(email_msg), level='error')
logger.error('Queue Error: %s', json.dumps(email_msg), exc_info=True)
async def job_handler(status: str):
"""Use schedule task to process notifications from db."""
db_session = APP.db_session
try:
logger.info('Schedule Job for sending %s email run at:%s', status,
datetime.utcnow())
notifications = await NotifyService.find_notifications_by_status(
db_session, status)
for notification in notifications:
logger.info('Process notificaiton id: %s', notification.id)
await process_notification(notification.id, db_session)
logger.info('Schedule Job for sending %s email finish at:%s', status,
datetime.utcnow())
except Exception: # pylint: disable=broad-except
logger.info('Notify Job Error: %s', exc_info=True)
finally:
db_session.close()
async def _process_consolidated_invoices(row):
target_txn_status = _get_row_value(row, Column.TARGET_TXN_STATUS)
if (target_txn :=
_get_row_value(row,
Column.TARGET_TXN)) == TargetTransaction.INV.value:
inv_number = _get_row_value(row, Column.TARGET_TXN_NO)
record_type = _get_row_value(row, Column.RECORD_TYPE)
logger.debug('Processing invoice : %s', inv_number)
inv_references: List[InvoiceReferenceModel] = db.session.query(InvoiceReferenceModel). \
filter(InvoiceReferenceModel.status_code == InvoiceReferenceStatus.ACTIVE.value). \
filter(InvoiceReferenceModel.invoice_number == inv_number). \
all()
payment_account: PaymentAccountModel = _get_payment_account(row)
if target_txn_status.lower() == Status.PAID.value.lower():
logger.debug('Fully PAID payment.')
await _process_paid_invoices(inv_references, row)
await _publish_mailer_events('PAD.PaymentSuccess', payment_account,
row)
elif target_txn_status.lower() == Status.NOT_PAID.value.lower() \
or record_type in (RecordType.PADR.value, RecordType.PAYR.value):
logger.info('NOT PAID. NSF identified.')
# NSF Condition. Publish to account events for NSF.
_process_failed_payments(row)
# Send mailer and account events to update status and send email notification
await _publish_account_events('lockAccount', payment_account, row)
else:
logger.error(
'Target Transaction Type is received as %s for PAD, and cannot process %s.',
target_txn, row)
capture_message(
'Target Transaction Type is received as {target_txn} for PAD, and cannot process.'
.format(target_txn=target_txn),
level='error')
async def cb_subscription_handler(msg: nats.aio.client.Msg):
"""Use Callback to process Queue Msg objects."""
db_session = APP.db_session
try:
logger.info('Received raw message seq:%s, data= %s', msg.sequence,
msg.data.decode())
notification_id = json.loads(msg.data.decode('utf-8'))
logger.info('Extracted id: %s', notification_id)
await process_notification(notification_id, db_session)
except (QueueException, Exception): # pylint: disable=broad-except
logger.info('Notify Queue Error: %s', exc_info=True)
finally:
db_session.close()
async def run(loop, mode, auth_account_id, auth_account_name, bank_number,
bank_branch_number, bank_account_number, order_number,
transaction_amount, transaction_id): # pylint: disable=too-many-locals
"""Run the main application loop for the service.
This runs the main top level service functions for working with the Queue.
"""
from entity_queue_common.service_utils import error_cb, logger, signal_handler
# NATS client connections
nc = NATS()
sc = STAN()
async def close():
"""Close the stream and nats connections."""
await sc.close()
await nc.close()
# Connection and Queue configuration.
def nats_connection_options():
return {
'servers':
os.getenv('NATS_SERVERS', 'nats://127.0.0.1:4222').split(','),
'io_loop':
loop,
'error_cb':
error_cb,
'name':
os.getenv('NATS_CLIENT_NAME', 'entity.filing.tester')
}
def stan_connection_options():
return {
'cluster_id': os.getenv('NATS_CLUSTER_ID', 'test-cluster'),
'client_id': str(random.SystemRandom().getrandbits(0x58)),
'nats': nc
}
def subscription_options():
return {
'subject':
os.getenv('NATS_SUBJECT', 'account.events'),
'queue':
os.getenv('NATS_QUEUE', 'account.events.worker'),
'durable_name':
os.getenv('NATS_QUEUE', 'account.events.worker') + '_durable'
}
try:
# Connect to the NATS server, and then use that for the streaming connection.
await nc.connect(**nats_connection_options())
await sc.connect(**stan_connection_options())
# register the signal handler
for sig in ('SIGINT', 'SIGTERM'):
loop.add_signal_handler(
getattr(signal, sig),
functools.partial(signal_handler,
sig_loop=loop,
sig_nc=nc,
task=close))
payload = None
if mode == 'lock':
payload = {
'specversion': '1.x-wip',
'type': 'bc.registry.payment.lockAccount',
'source':
f'https://api.pay.bcregistry.gov.bc.ca/v1/accounts/{auth_account_id}',
'id': f'{auth_account_id}',
'time': f'{datetime.now()}',
'datacontenttype': 'application/json',
'data': {
'accountId': auth_account_id,
'paymentMethod': 'PAD',
'outstandingAmount': transaction_amount,
'originalAmount': transaction_amount,
'amount': -float(transaction_amount)
}
}
elif mode == 'unlock':
payload = {
'specversion': '1.x-wip',
'type': 'bc.registry.payment.unlockAccount',
'source':
f'https://api.pay.bcregistry.gov.bc.ca/v1/accounts/{auth_account_id}',
'id': f'{auth_account_id}',
'time': f'{datetime.now()}',
'datacontenttype': 'application/json',
'data': {
'accountId': auth_account_id,
'accountName': auth_account_name,
'padTosAcceptedBy': ''
}
}
logger.info(payload)
await sc.publish(subject=subscription_options().get('subject'),
payload=json.dumps(payload).encode('utf-8'))
except Exception as e: # pylint: disable=broad-except
# TODO tighten this error and decide when to bail on the infinite reconnect
logger.error(e)
async def process_name_events(event_message: Dict[str, any]):
"""Process name events.
1. Check if the NR already exists in entities table, if yes apply changes. If not create entity record.
2. Check if new status is DRAFT, if yes call pay-api and get the account details for the payments against the NR.
3. If an account is found, affiliate to that account.
Args:
event_message (object): cloud event message, sample below.
{
'specversion': '1.0.1',
'type': 'bc.registry.names.events',
'source': '/requests/6724165',
'id': id,
'time': '',
'datacontenttype': 'application/json',
'identifier': '781020202',
'data': {
'request': {
'nrNum': 'NR 5659951',
'newState': 'APPROVED',
'previousState': 'DRAFT'
}
}
}
"""
logger.debug('>>>>>>>process_name_events>>>>>')
request_data = event_message.get('data').get('request')
nr_number = request_data['nrNum']
nr_status = request_data['newState']
nr_entity = EntityModel.find_by_business_identifier(nr_number)
if nr_entity is None:
logger.info('Entity doesn' 't exist, creating a new entity.')
nr_entity = EntityModel(business_identifier=nr_number,
corp_type_code=CorpType.NR.value)
nr_entity.status = nr_status
nr_entity.name = request_data.get(
'name',
'') # its not part of event now, this is to handle if they include it.
nr_entity.last_modified_by = None # TODO not present in event message.
nr_entity.last_modified = parser.parse(event_message.get('time'))
if nr_status == 'DRAFT' and AffiliationModel.find_affiliations_by_business_identifier(
nr_number) is None:
logger.info('Status is DRAFT, getting invoices for account')
# Find account details for the NR.
invoices = RestService.get(
f'{APP_CONFIG.PAY_API_URL}/payment-requests?businessIdentifier={nr_number}',
token=RestService.get_service_account_token()).json()
# Ideally there should be only one or two (priority fees) payment request for the NR.
if invoices and (auth_account_id := invoices['invoices'][0].get('paymentAccount').get('accountId')) \
and str(auth_account_id).isnumeric():
logger.info('Account ID received : %s', auth_account_id)
# Auth account id can be service account value too, so doing a query lookup than find_by_id
org: OrgModel = db.session.query(OrgModel).filter(
OrgModel.id == auth_account_id).one_or_none()
if org:
nr_entity.pass_code_claimed = True
# Create an affiliation.
logger.info(
'Creating affiliation between Entity : %s and Org : %s',
nr_entity, org)
affiliation: AffiliationModel = AffiliationModel(
entity=nr_entity, org=org)
affiliation.flush()
async def reconcile_payments(msg: Dict[str, any]):
"""Read the file and update payment details.
1: Parse the file and create a dict per row for easy access.
2: If the transaction is for invoice,
2.1 : If transaction status is PAID, update invoice and payment statuses, publish to account mailer.
For Online Banking invoices, publish message to the payment queue.
2.2 : If transaction status is NOT PAID, update payment status, publish to account mailer and events to handle NSF.
2.3 : If transaction status is PARTIAL, update payment and invoice status, publish to account mailer.
3: If the transaction is On Account for Credit, apply the credit to the account.
"""
file_name: str = msg.get('data').get('fileName')
minio_location: str = msg.get('data').get('location')
file = get_object(minio_location, file_name)
content = file.data.decode('utf-8-sig')
# Iterate the rows and create key value pair for each row
for row in csv.DictReader(content.splitlines()):
# Convert lower case keys to avoid any key mismatch
row = dict((k.lower(), v) for k, v in row.items())
logger.debug('Processing %s', row)
# IF not PAD and application amount is zero, continue
record_type = _get_row_value(row, Column.RECORD_TYPE)
pad_record_types: Tuple[str] = (RecordType.PAD.value,
RecordType.PADR.value,
RecordType.PAYR.value)
if float(_get_row_value(row, Column.APP_AMOUNT)
) == 0 and record_type not in pad_record_types:
continue
# If PAD, lookup the payment table and mark status based on the payment status
# If BCOL, lookup the invoices and set the status:
# Create payment record by looking the receipt_number
# If EFT/WIRE, lookup the invoices and set the status:
# Create payment record by looking the receipt_number
# PS : Duplicating some code to make the code more readable.
if record_type in pad_record_types:
# Handle invoices
await _process_consolidated_invoices(row)
elif record_type in (RecordType.BOLP.value, RecordType.EFTP.value):
# EFT, WIRE and Online Banking are one-to-one invoice. So handle them in same way.
await _process_unconsolidated_invoices(row)
elif record_type in (RecordType.ONAC.value, RecordType.CMAP.value,
RecordType.DRWP.value):
await _process_credit_on_invoices(row)
elif record_type == RecordType.ADJS.value:
logger.info('Adjustment received for %s.', msg)
else:
# For any other transactions like DM log error and continue.
logger.error(
'Record Type is received as %s, and cannot process %s.',
record_type, msg)
capture_message(
f'Record Type is received as {record_type}, and cannot process {msg}.',
level='error')
# Continue processing
# Commit the transaction and process next row.
db.session.commit()
# Create payment records for lines other than PAD
await _create_payment_records(content)
# Create Credit Records.
_create_credit_records(content)
# Sync credit memo and on account credits with CFS
_sync_credit_records()
async def _process_jv_details_feedback(ejv_file, has_errors, line,
receipt_number): # pylint:disable=too-many-locals
journal_name: str = line[7:17] # {ministry}{ejv_header_model.id:0>8}
ejv_header_model_id = int(journal_name[2:])
invoice_id = int(line[205:315])
invoice: InvoiceModel = InvoiceModel.find_by_id(invoice_id)
invoice_link: EjvInvoiceLinkModel = db.session.query(
EjvInvoiceLinkModel).filter(
EjvInvoiceLinkModel.ejv_header_id == ejv_header_model_id).filter(
EjvInvoiceLinkModel.invoice_id == invoice_id).one_or_none()
invoice_return_code = line[315:319]
invoice_return_message = line[319:469]
# If the JV process failed, then mark the GL code against the invoice to be stopped
# for further JV process for the credit GL.
logger.info('Is Credit or Debit %s - %s', line[104:105],
ejv_file.file_type)
if line[104:
105] == 'C' and ejv_file.file_type == EjvFileType.DISBURSEMENT.value:
disbursement_status = _get_disbursement_status(invoice_return_code)
invoice_link.disbursement_status_code = disbursement_status
invoice_link.message = invoice_return_message
logger.info('disbursement_status %s', disbursement_status)
if disbursement_status == DisbursementStatus.ERRORED.value:
has_errors = True
invoice.disbursement_status_code = DisbursementStatus.ERRORED.value
line_items: List[PaymentLineItemModel] = invoice.payment_line_items
for line_item in line_items:
# Line debit distribution
debit_distribution: DistributionCodeModel = DistributionCodeModel \
.find_by_id(line_item.fee_distribution_id)
credit_distribution: DistributionCodeModel = DistributionCodeModel \
.find_by_id(debit_distribution.disbursement_distribution_code_id)
credit_distribution.stop_ejv = True
else:
await _update_invoice_status(invoice)
elif line[104:
105] == 'D' and ejv_file.file_type == EjvFileType.PAYMENT.value:
# This is for gov account payment JV.
invoice_link.disbursement_status_code = _get_disbursement_status(
invoice_return_code)
invoice_link.message = invoice_return_message
logger.info('Invoice ID %s', invoice_id)
inv_ref: InvoiceReferenceModel = InvoiceReferenceModel.find_reference_by_invoice_id_and_status(
invoice_id, InvoiceReferenceStatus.ACTIVE.value)
logger.info('invoice_link.disbursement_status_code %s',
invoice_link.disbursement_status_code)
if invoice_link.disbursement_status_code == DisbursementStatus.ERRORED.value:
has_errors = True
# Cancel the invoice reference.
if inv_ref:
inv_ref.status_code = InvoiceReferenceStatus.CANCELLED.value
# Find the distribution code and set the stop_ejv flag to TRUE
dist_code: DistributionCodeModel = DistributionCodeModel.find_by_active_for_account(
invoice.payment_account_id)
dist_code.stop_ejv = True
elif invoice_link.disbursement_status_code == DisbursementStatus.COMPLETED.value:
# Set the invoice status as REFUNDED if it's a JV reversal, else mark as PAID
is_reversal = invoice.invoice_status_code in (
InvoiceStatus.REFUNDED.value,
InvoiceStatus.REFUND_REQUESTED.value)
invoice.invoice_status_code = InvoiceStatus.REFUNDED.value if is_reversal else InvoiceStatus.PAID.value
# Mark the invoice reference as COMPLETED, create a receipt
if inv_ref:
inv_ref.status_code = InvoiceReferenceStatus.COMPLETED.value
# Find receipt and add total to it, as single invoice can be multiple rows in the file
if not is_reversal:
receipt = ReceiptModel.find_by_invoice_id_and_receipt_number(
invoice_id=invoice_id, receipt_number=receipt_number)
if receipt:
receipt.receipt_amount += float(line[89:104])
else:
ReceiptModel(invoice_id=invoice_id,
receipt_number=receipt_number,
receipt_date=datetime.now(),
receipt_amount=float(line[89:104])).flush()
return has_errors
# Create payment record by looking the receipt_number
# If EFT/WIRE, lookup the invoices and set the status:
# Create payment record by looking the receipt_number
# PS : Duplicating some code to make the code more readable.
if (record_type := _get_row_value(row, Column.RECORD_TYPE)) \
in (RecordType.PAD.value, RecordType.PADR.value, RecordType.PAYR.value):
# Handle invoices
await _process_consolidated_invoices(row)
elif record_type in (RecordType.BOLP.value, RecordType.EFTP.value):
# EFT, WIRE and Online Banking are one-to-one invoice. So handle them in same way.
await _process_unconsolidated_invoices(row)
elif record_type in (RecordType.ONAC.value, RecordType.CMAP.value,
RecordType.DRWP.value):
await _process_credit_on_invoices(row)
elif record_type == RecordType.ADJS.value:
logger.info('Adjustment received for %s.', msg)
else:
# For any other transactions like DM log error and continue.
logger.error(
'Record Type is received as %s, and cannot process %s.',
record_type, msg)
capture_message(
'Record Type is received as {record_type}, and cannot process {msg}.'
.format(record_type=record_type, msg=msg),
level='error')
# Continue processing
# Commit the transaction and process next row.
db.session.commit()
# Create payment records for lines other than PAD
