def _save_bank_details(cls, access_token, party_number: str, # pylint: disable=too-many-arguments account_number: str, site_number: str, payment_info: Dict[str, str]): """Save bank details to the site.""" current_app.logger.debug('<Creating CFS payment details ') site_payment_url = current_app.config.get( 'CFS_BASE_URL') + f'/cfs/parties/{party_number}/accs/{account_number}/sites/{site_number}/payment/' bank_number = str(payment_info.get('bankInstitutionNumber')) branch_number = str(payment_info.get('bankTransitNumber')) # bank account name should match legal name name = re.sub(r'[^a-zA-Z0-9]+', ' ', payment_info.get('bankAccountName', '')) payment_details: Dict[str, str] = { 'bank_account_name': name[:30], 'bank_number': f'{bank_number:0>4}', 'branch_number': f'{branch_number:0>5}', 'bank_account_number': str(payment_info.get('bankAccountNumber')), 'country_code': DEFAULT_COUNTRY, 'currency_code': DEFAULT_CURRENCY } site_payment_response = OAuthService.post(site_payment_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, payment_details).json() payment_details = { 'bank_account_number': payment_info.get('bankAccountNumber'), 'bank_number': bank_number, 'bank_branch_number': branch_number, 'payment_instrument_number': site_payment_response.get('payment_instrument_number') } current_app.logger.debug('>Creating CFS payment details') return payment_details
def send_email(cls, token, recipients: str, html_body: str): # pylint:disable=unused-argument """Send the email asynchronously, using the given details.""" subject = 'Your BC Registries statement is available' current_app.logger.info(f'send_email to recipients: {recipients}') notify_url = current_app.config.get('NOTIFY_API_URL') + '/notify/' notify_body = { 'recipients': recipients, 'content': { 'subject': subject, 'body': html_body } } notify_response = OAuthService.post( notify_url, token=token, auth_header_type=AuthHeaderType.BEARER, content_type=ContentType.JSON, data=notify_body) current_app.logger.info('send_email notify_response') if notify_response: response_json = json.loads(notify_response.text) if response_json['notifyStatus']['code'] != 'FAILURE': return True return False
def create_daily_reports(cls, date: str, **kwargs): """Create and return daily report for the day provided.""" routing_slips: List[RoutingSlipModel] = RoutingSlipModel.search( dict( dateFilter=dict( endDate=date, startDate=date, target='created_on' ) ), page=1, limit=0, return_all=True )[0] total: float = 0 no_of_cash: int = 0 no_of_cheque: int = 0 total_cash_usd: float = 0 total_cheque_usd: float = 0 total_cash_cad: float = 0 total_cheque_cad: float = 0 # TODO Only CAD supported now, so just add up the total. for routing_slip in routing_slips: total += float(routing_slip.total) if routing_slip.payment_account.payment_method == PaymentMethod.CASH.value: no_of_cash += 1 # TODO check if the payment is CAD or USD. total_cash_cad += float(routing_slip.total) if routing_slip.total_usd is not None: total_cash_usd += float(routing_slip.total_usd) else: no_of_cheque += 1 total_cheque_cad += float(routing_slip.total) if routing_slip.total_usd is not None: total_cheque_usd += float(routing_slip.total_usd) report_dict = dict( templateName='routing_slip_report', reportName=f'Routing-Slip-Daily-Report-{date}', templateVars=dict( day=date, reportDay=str(get_local_time(datetime.now())), total=total, numberOfCashReceipts=no_of_cash, numberOfChequeReceipts=no_of_cheque, totalCashInUsd=total_cash_usd, totalChequeInUsd=total_cheque_usd, totalCashInCad=total_cash_cad, totalChequeInCad=total_cheque_cad ) ) pdf_response = OAuthService.post(current_app.config.get('REPORT_API_BASE_URL'), kwargs['user'].bearer_token, AuthHeaderType.BEARER, ContentType.JSON, report_dict) return pdf_response, report_dict.get('reportName')
def _update_site(cls, cfs_account: CfsAccountModel, receipt_method: str): access_token = CFSService.get_token().json().get('access_token') pad_stop_payload = { 'receipt_method': receipt_method } cfs_base: str = current_app.config.get('CFS_BASE_URL') site_url = f'{cfs_base}/cfs/parties/{cfs_account.cfs_party}/accs/{cfs_account.cfs_account}/' \ f'sites/{cfs_account.cfs_site}/' site_update_response = OAuthService.post(site_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, pad_stop_payload, is_put=True) return site_update_response.json()
def _create_party(access_token: str = None, party_name: str = None): """Create a party record in PayBC.""" current_app.logger.debug('<Creating party Record') party_url = current_app.config.get('CFS_BASE_URL') + '/cfs/parties/' party: Dict[str, Any] = { 'customer_name': party_name } party_response = OAuthService.post(party_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, party) current_app.logger.debug('>Creating party Record') return party_response.json()
def get_fas_token(): """Generate oauth token for FAS client which will be used for all communication.""" current_app.logger.debug('<Getting FAS token') token_url = current_app.config.get('CFS_BASE_URL', None) + '/oauth/token' basic_auth_encoded = base64.b64encode( bytes(current_app.config.get('CFS_FAS_CLIENT_ID') + ':' + current_app.config.get('CFS_FAS_CLIENT_SECRET'), 'utf-8')).decode('utf-8') data = 'grant_type=client_credentials' token_response = OAuthService.post(token_url, basic_auth_encoded, AuthHeaderType.BASIC, ContentType.FORM_URL_ENCODED, data) current_app.logger.debug('>Getting FAS token') return token_response
def test_post(app): """Test Post.""" with app.app_context(): mock_get_token = patch('pay_api.services.oauth_service.requests.post') mock_get = mock_get_token.start() mock_get.return_value = Mock(status_code=201) mock_get.return_value.json.return_value = {} get_token_response = OAuthService.post('http://google.com/', '', AuthHeaderType.BEARER, ContentType.JSON, {}) mock_get_token.stop() assert get_token_response.json() == {}
def _create_paybc_account(access_token, party, is_fas: bool): """Create account record in PayBC.""" current_app.logger.debug('<Creating CFS account') account_url = current_app.config.get('CFS_BASE_URL') + f"/cfs/parties/{party.get('party_number', None)}/accs/" account: Dict[str, Any] = { 'account_description': current_app.config.get('CFS_ACCOUNT_DESCRIPTION'), 'customer_profile_class': CFS_FAS_CUSTOMER_PROFILE_CLASS if is_fas else CFS_CUSTOMER_PROFILE_CLASS } account_response = OAuthService.post(account_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, account) current_app.logger.debug('>Creating CFS account') return account_response.json()
def get_token(): issuer_url = current_app.config.get('JWT_OIDC_ISSUER') token_url = issuer_url + '/protocol/openid-connect/token' # https://sso-dev.pathfinder.gov.bc.ca/auth/realms/fcf0kpqr/protocol/openid-connect/token basic_auth_encoded = base64.b64encode( bytes(current_app.config.get('KEYCLOAK_SERVICE_ACCOUNT_ID') + ':' + current_app.config.get( 'KEYCLOAK_SERVICE_ACCOUNT_SECRET'), 'utf-8')).decode('utf-8') data = 'grant_type=client_credentials' token_response = OAuthService.post(token_url, basic_auth_encoded, AuthHeaderType.BASIC, ContentType.FORM_URL_ENCODED, data) token = token_response.json().get('access_token') return token
def validate_bank_account(bank_details: Tuple[Dict[str, Any]]) -> Dict[str, str]: """Validate bank details by invoking CFS validation Service.""" current_app.logger.debug('<Validating bank account details') validation_url = current_app.config.get('CFS_BASE_URL') + '/cfs/validatepayins/' bank_number = str(bank_details.get('bankInstitutionNumber', None)) branch_number = str(bank_details.get('bankTransitNumber', None)) bank_details: Dict[str, str] = { 'accountNumber': bank_details.get('bankAccountNumber', None), 'branchNumber': f'{branch_number:0>5}', 'bankNumber': f'{bank_number:0>4}', } try: access_token = CFSService.get_token().json().get('access_token') # raise_for_error should be false so that HTTPErrors are not thrown.PAYBC sends validation errors as 404 bank_validation_response_obj = OAuthService.post(validation_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, bank_details, raise_for_error=False) if bank_validation_response_obj.status_code in (HTTPStatus.OK.value, HTTPStatus.BAD_REQUEST.value): bank_validation_response = bank_validation_response_obj.json() validation_response = { 'bank_number': bank_validation_response.get('bank_number', None), 'bank_name': bank_validation_response.get('bank_number', None), 'branch_number': bank_validation_response.get('branch_number', None), 'transit_address': bank_validation_response.get('transit_address', None), 'account_number': bank_validation_response.get('account_number', None), 'is_valid': bank_validation_response.get('CAS-Returned-Messages', None) == 'VALID', 'status_code': HTTPStatus.OK.value, 'message': CFSService._transform_error_message( bank_validation_response.get('CAS-Returned-Messages')) } else: current_app.logger.debug('<Bank validation HTTP exception- {}', bank_validation_response_obj.text) validation_response = { 'status_code': bank_validation_response_obj.status_code, 'message': ['Bank validation service cant be reached'] } except ServiceUnavailableException as exc: # suppress all other errors current_app.logger.debug('<Bank validation ServiceUnavailableException exception- {}', exc.error) validation_response = { 'status_code': HTTPStatus.SERVICE_UNAVAILABLE.value, 'message': [str(exc.error)] } return validation_response
def _create_site(access_token, account, contact_info, receipt_method): """Create site in PayBC.""" current_app.logger.debug('<Creating site ') if not contact_info: contact_info = {} site_url = current_app.config.get('CFS_BASE_URL') + '/cfs/parties/{}/accs/{}/sites/' \ .format(account.get('party_number', None), account.get('account_number', None)) site: Dict[str, Any] = { 'site_name': 'Site 1', # Make it dynamic if we ever need multiple sites per account 'city': get_non_null_value(contact_info.get('city'), DEFAULT_CITY), 'address_line_1': get_non_null_value(contact_info.get('addressLine1'), DEFAULT_ADDRESS_LINE_1), 'postal_code': get_non_null_value(contact_info.get('postalCode'), DEFAULT_POSTAL_CODE).replace(' ', ''), 'province': get_non_null_value(contact_info.get('province'), DEFAULT_JURISDICTION), 'country': get_non_null_value(contact_info.get('country'), DEFAULT_COUNTRY), 'customer_site_id': '1', 'primary_bill_to': 'Y', 'customer_profile_class': CFS_CUSTOMER_PROFILE_CLASS } if receipt_method: site['receipt_method'] = receipt_method try: site_response = OAuthService.post(site_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, site).json() except HTTPError as e: # If the site creation fails with 400, query and return site if e.response.status_code == 400: site_response = \ OAuthService.get(site_url, access_token, AuthHeaderType.BEARER, ContentType.JSON).json().get( 'items')[0] else: raise e current_app.logger.debug('>Creating site ') return site_response
def test_post_with_connection_errors(app): """Test Get with errors.""" with app.app_context(): mock_get_token = patch('pay_api.services.oauth_service.requests.post') mock_get = mock_get_token.start() mock_get.side_effect = HTTPError() mock_get.return_value.json.return_value = {} with pytest.raises(HTTPError) as excinfo: OAuthService.post('http://google.com/', '', AuthHeaderType.BEARER, ContentType.JSON, {}) assert excinfo.type == HTTPError mock_get_token.stop() with patch('pay_api.services.oauth_service.requests.post', side_effect=ConnectionError('mocked error')): with pytest.raises(ServiceUnavailableException) as excinfo: OAuthService.post('http://google.com/', '', AuthHeaderType.BEARER, ContentType.JSON, {}) assert excinfo.type == ServiceUnavailableException with patch('pay_api.services.oauth_service.requests.post', side_effect=ConnectTimeout('mocked error')): with pytest.raises(ServiceUnavailableException) as excinfo: OAuthService.post('http://google.com/', '', AuthHeaderType.BEARER, ContentType.JSON, {}) assert excinfo.type == ServiceUnavailableException
def update_failed_distributions(cls): # pylint:disable=too-many-locals """Update failed distributions. Steps: 1. Get all invoices with status UPDATE_REVENUE_ACCOUNT. 2. Find the completed invoice reference for the invoice. 3. Call the paybc GET service and check if there is any revenue not processed. 4. If yes, update the revenue details. 5. Update the invoice status as PAID and save. """ gl_updated_invoices = InvoiceModel.query.filter_by( invoice_status_code=InvoiceStatus.UPDATE_REVENUE_ACCOUNT.value).all() current_app.logger.debug(f'Found {len(gl_updated_invoices)} invoices to update revenue details.') if len(gl_updated_invoices) > 0: # pylint:disable=too-many-nested-blocks access_token: str = cls._get_token().json().get('access_token') paybc_ref_number: str = current_app.config.get('PAYBC_DIRECT_PAY_REF_NUMBER') paybc_svc_base_url = current_app.config.get('PAYBC_DIRECT_PAY_BASE_URL') for gl_updated_invoice in gl_updated_invoices: payment: PaymentModel = PaymentModel.find_payment_for_invoice(gl_updated_invoice.id) # For now handle only GL updates for Direct Pay, more to come in future if payment.payment_method_code != PaymentMethod.DIRECT_PAY.value: cls._update_invoice_status(gl_updated_invoice, InvoiceStatus.PAID.value) else: active_reference = list( filter(lambda reference: (reference.status_code == InvoiceReferenceStatus.COMPLETED.value), gl_updated_invoice.references))[0] payment_url: str = \ f'{paybc_svc_base_url}/paybc/payment/{paybc_ref_number}/{active_reference.invoice_number}' payment_details: dict = cls.get_payment_details(payment_url, access_token) if payment_details and payment_details.get('paymentstatus') == STATUS_PAID: has_gl_completed: bool = True for revenue in payment_details.get('revenue'): if revenue.get('glstatus') in STATUS_NOT_PROCESSED: has_gl_completed = False if not has_gl_completed: post_revenue_payload = { 'revenue': [] } invoice: InvoiceService = InvoiceService.find_by_id(identifier=gl_updated_invoice.id, skip_auth_check=True) payment_line_items = PaymentLineItemModel.find_by_invoice_ids([invoice.id]) index: int = 0 for payment_line_item in payment_line_items: distribution_code = DistributionCodeModel.find_by_id( payment_line_item.fee_distribution_id) index = index + 1 post_revenue_payload['revenue'].append( cls.get_revenue_details(index, distribution_code, payment_line_item.total)) if payment_line_item.service_fees is not None and payment_line_item.service_fees > 0: index = index + 1 post_revenue_payload['revenue'].append( cls.get_revenue_details(index, distribution_code, payment_line_item.service_fees, is_service_fee=True)) OAuthService.post(payment_url, access_token, AuthHeaderType.BEARER, ContentType.JSON, post_revenue_payload) cls._update_invoice_status(gl_updated_invoice, InvoiceStatus.PAID.value)
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.add_column( 'invoice', sa.Column('created_name', sa.String(length=100), nullable=True)) op.add_column( 'invoice', sa.Column('updated_name', sa.String(length=100), nullable=True)) op.add_column( 'payment', sa.Column('created_name', sa.String(length=100), nullable=True)) op.add_column( 'payment', sa.Column('updated_name', sa.String(length=100), nullable=True)) # Check if there are records in payment or invoice table to update names conn = op.get_bind() res = conn.execute(f"select id from payment;") pay_results = res.fetchall() res = conn.execute(f"select id from invoice;") inv_results = res.fetchall() if len(pay_results) > 0 or len(inv_results) > 0: # Now call Keycloak API and update the names for existing record config = current_app.config issuer_url = config.get('JWT_OIDC_ISSUER') token_url = issuer_url + '/protocol/openid-connect/token' # https://sso-dev.pathfinder.gov.bc.ca/auth/realms/fcf0kpqr/protocol/openid-connect/token basic_auth_encoded = base64.b64encode( bytes( config.get('KEYCLOAK_SERVICE_ACCOUNT_ID') + ':' + config.get('KEYCLOAK_SERVICE_ACCOUNT_SECRET'), 'utf-8')).decode('utf-8') data = 'grant_type=client_credentials' token_response = OAuthService.post(token_url, basic_auth_encoded, AuthHeaderType.BASIC, ContentType.FORM_URL_ENCODED, data) token = token_response.json().get('access_token') get_users_url = issuer_url.replace( '/auth/', '/auth/admin/') + '/users?max=999999999' get_users_response = OAuthService.get(get_users_url, token, AuthHeaderType.BEARER, ContentType.JSON) for user in get_users_response.json(): if user.get('firstName', None) or user.get('lastName', None): user_name = user['username'].lower() display_name = user.get('firstName', '') + ' ' + user.get( 'lastName', '') op.execute( f"update payment set created_name = '{display_name}' where lower(created_by) = '{user_name}'" ) op.execute( f"update payment set updated_name = '{display_name}' where lower(updated_by) = '{user_name}'" ) op.execute( f"update invoice set created_name = '{display_name}' where lower(created_by) = '{user_name}'" ) op.execute( f"update invoice set updated_name = '{display_name}' where lower(updated_by) = '{user_name}'" )
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.create_table( 'bcol_payment_account', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('bcol_user_id', sa.String(length=50), nullable=True), sa.Column('bcol_account_id', sa.String(length=50), nullable=True), sa.Column('account_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ['account_id'], ['payment_account.id'], ), sa.PrimaryKeyConstraint('id')) op.create_index(op.f('ix_bcol_payment_account_account_id'), 'bcol_payment_account', ['account_id'], unique=False) op.create_index(op.f('ix_bcol_payment_account_bcol_account_id'), 'bcol_payment_account', ['bcol_account_id'], unique=False) op.create_index(op.f('ix_bcol_payment_account_bcol_user_id'), 'bcol_payment_account', ['bcol_user_id'], unique=False) op.create_table( 'credit_payment_account', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('corp_number', sa.String(length=20), nullable=True), sa.Column('corp_type_code', sa.String(length=10), nullable=True), sa.Column('paybc_account', sa.String(length=50), nullable=True), sa.Column('paybc_party', sa.String(length=50), nullable=True), sa.Column('paybc_site', sa.String(length=50), nullable=True), sa.Column('account_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ['account_id'], ['payment_account.id'], ), sa.ForeignKeyConstraint( ['corp_type_code'], ['corp_type.code'], ), sa.PrimaryKeyConstraint('id')) op.create_index(op.f('ix_credit_payment_account_account_id'), 'credit_payment_account', ['account_id'], unique=False) op.create_index(op.f('ix_credit_payment_account_paybc_account'), 'credit_payment_account', ['paybc_account'], unique=False) op.create_table( 'internal_payment_account', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('corp_number', sa.String(length=20), nullable=True), sa.Column('corp_type_code', sa.String(length=10), nullable=True), sa.Column('account_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ['account_id'], ['payment_account.id'], ), sa.ForeignKeyConstraint( ['corp_type_code'], ['corp_type.code'], ), sa.PrimaryKeyConstraint('id')) op.create_index(op.f('ix_internal_payment_account_account_id'), 'internal_payment_account', ['account_id'], unique=False) op.add_column('invoice', sa.Column('bcol_account_id', sa.Integer(), nullable=True)) op.add_column('invoice', sa.Column('credit_account_id', sa.Integer(), nullable=True)) op.add_column( 'invoice', sa.Column('internal_account_id', sa.Integer(), nullable=True)) op.drop_constraint('invoice_account_id_fkey', 'invoice', type_='foreignkey') op.create_foreign_key(None, 'invoice', 'bcol_payment_account', ['bcol_account_id'], ['id']) op.create_foreign_key(None, 'invoice', 'credit_payment_account', ['credit_account_id'], ['id']) op.create_foreign_key(None, 'invoice', 'internal_payment_account', ['internal_account_id'], ['id']) op.add_column( 'invoice', sa.Column('business_identifier', sa.String(length=20), nullable=True)) op.add_column( 'invoice', sa.Column('corp_type_code', sa.String(length=10), nullable=True)) op.create_foreign_key(None, 'invoice', 'corp_type', ['corp_type_code'], ['code']) # Create temp variable to keep the payment accounts payment_accounts: [] = [] conn = op.get_bind() res = conn.execute( f"select id,corp_number,corp_type_code,payment_system_code,account_number,party_number,site_number from payment_account;" ) results = res.fetchall() for result in results: payment_accounts.append({ "id": result[0], "corp_number": result[1], "corp_type_code": result[2], "payment_system_code": result[3], "account_number": result[4], "party_number": result[5], "site_number": result[6] }) # Now drop the columns from payment_account table op.drop_index('ix_payment_account_account_number', table_name='payment_account') op.drop_index('ix_payment_account_bcol_account_id', table_name='payment_account') op.drop_index('ix_payment_account_bcol_user_id', table_name='payment_account') op.drop_constraint('payment_account_payment_system_code_fkey', 'payment_account', type_='foreignkey') op.drop_constraint('payment_account_corp_type_code_fkey', 'payment_account', type_='foreignkey') op.drop_column('payment_account', 'payment_system_code') op.drop_column('payment_account', 'corp_type_code') op.drop_column('payment_account', 'site_number') op.drop_column('payment_account', 'bcol_user_id') op.drop_column('payment_account', 'account_number') op.drop_column('payment_account', 'bcol_account_id') op.drop_column('payment_account', 'corp_number') op.drop_column('payment_account', 'party_number') # Delete all records from payment_account op.execute('delete from payment_account;') # Create admin token to call auth-api config = current_app.config token_url = config.get( 'JWT_OIDC_ISSUER' ) + '/protocol/openid-connect/token' # https://sso-dev.pathfinder.gov.bc.ca/auth/realms/fcf0kpqr/protocol/openid-connect/token basic_auth_encoded = base64.b64encode( bytes( config.get('KEYCLOAK_SERVICE_ACCOUNT_ID') + ':' + config.get('KEYCLOAK_SERVICE_ACCOUNT_SECRET'), 'utf-8')).decode('utf-8') data = 'grant_type=client_credentials' token_response = OAuthService.post(token_url, basic_auth_encoded, AuthHeaderType.BASIC, ContentType.FORM_URL_ENCODED, data) token = token_response.json().get('access_token') payment_account_table = table('payment_account', column('auth_account_id', String)) credit_payment_account_table = table('credit_payment_account', column('corp_number', String), column('corp_type_code', String), column('paybc_account', String), column('paybc_party', String), column('paybc_site', String), column('account_id', Integer)) internal_payment_account_table = table('internal_payment_account', column('corp_number', String), column('corp_type_code', String), column('account_id', Integer)) for payment_account in payment_accounts: corp_number = payment_account.get('corp_number') payment_system_code = payment_account.get('payment_system_code') corp_type_code = payment_account.get('corp_type_code') account_number = payment_account.get('account_number') party_number = payment_account.get('party_number') site_number = payment_account.get('site_number') id = payment_account.get('id') if corp_number: # Call Auth-API to get the auth_account id auth_search_url = config.get( 'AUTH_API_ENDPOINT') + 'orgs?affiliation=' + corp_number.upper( ) orgs_response = None try: orgs_response = OAuthService.get( auth_search_url, token, AuthHeaderType.BEARER, ContentType.JSON).json().get('orgs') except Exception as e: print( f'--- Error Occured while getting org for affiliation {corp_number}' ) if orgs_response and orgs_response[0]: auth_account_id = str(orgs_response[0].get('id')) else: auth_account_id = f'PASSCODE_ACCOUNT_{corp_number}' res = conn.execute( f"select id from payment_account where auth_account_id = '{auth_account_id}'" ) results = res.fetchall() credit_payment_account_id = None internal_payment_account_id = None if results and results[0]: payment_account_id = results[0][0] else: op.bulk_insert(payment_account_table, [{ "auth_account_id": auth_account_id }]) res = conn.execute( f"select id from payment_account where auth_account_id = '{auth_account_id}'" ) results = res.fetchall() payment_account_id = results[0][0] if payment_system_code == 'PAYBC': print(f'Creating credit account for {payment_account_id}') op.bulk_insert(credit_payment_account_table, [{ "corp_number": corp_number, "corp_type_code": corp_type_code, "paybc_account": account_number, "paybc_party": party_number, "paybc_site": site_number, "account_id": payment_account_id }]) res = conn.execute( f"select id from credit_payment_account where account_id = '{payment_account_id}' and corp_number='{corp_number}' and paybc_account='{account_number}'" ) results = res.fetchall() credit_payment_account_id = results[0][0] elif payment_system_code == 'INTERNAL': print( f'Creating internal payment account for {payment_account_id}' ) op.bulk_insert(internal_payment_account_table, [{ "corp_number": corp_number, "corp_type_code": corp_type_code, "account_id": payment_account_id }]) res = conn.execute( f"select id from internal_payment_account where account_id = '{payment_account_id}' and corp_number='{corp_number}' and corp_type_code='{corp_type_code}'" ) results = res.fetchall() internal_payment_account_id = results[0][0] print('Internal ', internal_payment_account_id) print('Credit ', credit_payment_account_id) if internal_payment_account_id: op.execute( f"update invoice set internal_account_id = '{internal_payment_account_id}', corp_type_code='{corp_type_code}', business_identifier='{corp_number}' where account_id = '{id}'" ) elif credit_payment_account_id: op.execute( f"update invoice set credit_account_id = '{credit_payment_account_id}', corp_type_code='{corp_type_code}', business_identifier='{corp_number}' where account_id = '{id}'" ) op.drop_column('invoice', 'account_id')