def get_ContactID(code = None):
if code is None:
url = 'https://api.xero.com/api.xro/2.0/Contacts'
contacts = utils.xero_get(url)
else:
# https://developer.xero.com/documentation/api/contacts#optimised-queryparameters
url = f'https://api.xero.com/api.xro/2.0/Contacts?where=AccountNumber=="{code}"'
contacts = utils.xero_get(url)
if len(contacts['Contacts'])>0:
return contacts['Contacts'][0]['ContactID']
else:
return None
def get_Xero_Contacts():
has_more_pages = True
page = 0
contacts = []
_contacts = {}
# Go through pages (100 per page)
while has_more_pages:
page += 1
print(f"{Fore.YELLOW} Processing Page {page} of contacts...")
url = f'https://api.xero.com/api.xro/2.0/Contacts?summaryOnly=True&page={page}'
xero_contacts = utils.xero_get(url)
if len(xero_contacts["Contacts"]) == 0:
has_more_pages = False
else:
_contacts = [
{
"memberCode":
_contact['AccountNumber'],
"Name":
_contact['FirstName'] + ' ' +
_contact['LastName'] if 'LastName' in _contact else '',
"ContactID":
_contact['ContactID']
} for _contact in xero_contacts['Contacts']
if ('AccountNumber' in _contact
# Exclude other contacts who have account numbers
and len(_contact['AccountNumber']) == 4 and
_contact['ContactStatus'] == 'ACTIVE')
]
contacts.extend(_contacts)
return contacts
def get_last_subscription_amount_by_contact_id(contact_id):
list_last_invoice = []
list_of_invoices = []
list_of_invoices = utils.xero_get(
f"https://api.xero.com/api.xro/2.0/Invoices?ContactIDs={contact_id}&Statuses=AUTHORISED,PAID"
)
if len(list_of_invoices["Invoices"]) > 0:
# Get latest subscription amount
list_last_invoice = [
x for x in list_of_invoices["Invoices"]
if x["InvoiceNumber"].startswith(
SEARCH_STRING_FOR_PREVIOUS_SUBSCRIPTION)
]
if len(list_last_invoice) == 1:
return list_last_invoice[0]["Total"]
elif len(list_last_invoice) == 0:
utils.my_logger.warn(
f"{member_code} has no previous subscription for '{SEARCH_STRING_FOR_PREVIOUS_SUBSCRIPTION}-*'; Skipping"
)
return None
elif len(list_last_invoice) > 1:
utils.my_logger.warn(
f"{member_code} has more than one subscription for '{SEARCH_STRING_FOR_PREVIOUS_SUBSCRIPTION}-*'; setting to 0"
)
return 0
else:
utils.my_logger.warn(f"No Invoices found for {member_code}")
return None
def get_member_txns(since_date):
print(
color(f"\nProcessing Member Transactions\n================",
Colors.blue))
_member_txns = {}
for bank_account in bank_accounts.items():
# Reset page counter for each account (DBS, NETS etc.)
has_more_pages = True
page = 0
# Go through pages (100 txns per page)
while has_more_pages:
page += 1
# This endpoint does not return payments applied to invoices, expense claims or transfers between bank accounts.
url = f'https://api.xero.com/api.xro/2.0/BankTransactions?where=BankAccount.Code=="{bank_account[1]}"&page={page}'
_header = {'If-Modified-Since': since_date}
txns = utils.xero_get(url, **_header)
if len(txns['BankTransactions']) == 0:
has_more_pages = False
else:
print(
color(f"Processing {bank_account[0]}. Page {page}",
Colors.blue))
# Keep only "Type":"RECEIVE" & construct a dict via Python List Comprehension
_receive_txns = [
{
# Build the output item
"ContactID": _txn['Contact']['ContactID'],
"ContactName": _txn['Contact']['Name'],
"BankAccount": _txn['BankAccount']['Name'],
# "Year": _txn['DateString'].split('-')[0],
"Year": str(utils.parse_Xero_Date(_txn['Date']).year),
# Nested dict
"Line Items": _txn['LineItems'],
"Net Amount": _txn['Total'],
"Status": _txn['Status']
} for _txn in txns['BankTransactions'] if (
# Only those tnxs that are payments to STOSC
_txn['Type'] == 'RECEIVE' and _txn['Status'] ==
'AUTHORISED' and _txn['IsReconciled'] == True)
]
receive_txns.extend(_receive_txns)
return receive_txns
def get_member_invoice_payments(since_date):
print(
color(f"Processing Member Subscriptions\n================",
Colors.blue))
has_more_pages = True
page = 0
# Go through pages (100 txns per page)
while has_more_pages:
page += 1
# This endpoint does not return payments applied to invoices, expense claims or transfers between bank accounts.
url = f'https://api.xero.com/api.xro/2.0/Payments?where=PaymentType="ACCRECPAYMENT"&page={page}'
_header = {'If-Modified-Since': since_date}
payments = utils.xero_get(url, **_header)
if len(payments['Payments']) == 0:
has_more_pages = False
else:
# Keep only "Type":"RECEIVE" & construct a dict via Python List Comprehension
_receive_payments = [
{
# Build the output item
"ContactID":
_payments['Invoice']['Contact']['ContactID'],
"ContactName":
_payments['Invoice']['Contact']['Name'],
# We assume any invoices not starting with INV is issued for harvest Festival
"AccountCode":
'3010'
if _payments['Invoice']['InvoiceNumber'].startswith('INV')
else '3200',
#"Year": _txn['DateString'].split('-')[0],
"Year":
str(utils.parse_Xero_Date(_payments['Date']).year),
"LineAmount":
_payments['Amount']
} for _payments in payments['Payments'] if (
# Only those tnxs that are payments to STOSC
_payments['Status'] == 'AUTHORISED'
and _payments['IsReconciled'] == True)
]
receive_payments.extend(_receive_payments)
return receive_payments
def get_member_invoice_payments(since_date):
"""
Get Invoices that are named `INV-20` where 20 represents the year.
Only check where Invoice status = AUTHORISED,PAID and startswith('INV').
The invoices are checked to see if they were modified at the begining of
this year (as they should be when creating new invoices after Jan 1).
If this check is not there, we will get all invoices from years past.
"""
has_more_pages = True
page = 0
# Go through pages (100 txns per page)
while has_more_pages:
page += 1
# This endpoint does not return payments applied to invoices, expense claims or transfers between bank accounts.
url = f'https://api.xero.com/api.xro/2.0/Invoices?page={page}&where=Type=="ACCREC"&Statuses=AUTHORISED,PAID&summaryonly=True'
_header = {"If-Modified-Since": since_date}
invoices = utils.xero_get(url, **_header)
if len(invoices["Invoices"]) == 0:
has_more_pages = False
else:
# Keep only "Type":"RECEIVE" & construct a dict via Python List Comprehension
_invoices = [
{
# Build the output item
"ContactName": _invoice["Contact"]["Name"],
"InvoiceNumber": _invoice["InvoiceNumber"],
"InvoiceDate":
utils.parse_Xero_Date(_invoice["Date"]).date(),
"InvoiceYear": "20" + _invoice["InvoiceNumber"][4:][:2],
"Total": _invoice["Total"],
"AmountDue": _invoice["AmountDue"],
"AmountPaid": _invoice["AmountPaid"],
} for _invoice in invoices["Invoices"] if (
# Only Subscription invoices and No Harvest Festival ones
_invoice["InvoiceNumber"].startswith("INV"))
]
receive_payments.extend(_invoices)
print(
color(f"Processed {len(receive_payments)} Subscriptions",
Colors.orange))
return receive_payments
color(f"Did you set NEXT Invoice Numbers? to INV-22-001? ",
Colors.red))
sys.exit(0)
utils.my_logger.info(f"Processing Member Subscriptions\n================",
Colors.blue)
has_more_pages = True
page = 0
# Go through pages (100 txns per page)
while has_more_pages:
page += 1
# This endpoint does not return payments applied to invoices, expense claims or transfers between bank accounts.
url = f'https://api.xero.com/api.xro/2.0/Payments?where=PaymentType="ACCRECPAYMENT"&page={page}'
_header = {'If-Modified-Since': since_date}
payments = utils.xero_get(url, **_header)
if len(payments['Payments']) == 0:
has_more_pages = False
else:
for _payments in payments['Payments']:
# Paid invoices without a Reference will not have the Reference field.
utils.my_logger.info(
f"For Invoice {_payments['Invoice']['InvoiceNumber']}. [https://invoicing.xero.com/view/{_payments['Invoice']['InvoiceID']}]"
)
if _payments['Status'] == 'AUTHORISED' and _payments[
'Reference'] == '':
if _payments['Invoice']['InvoiceNumber'].startswith('INV-21'):
updateInvoiceReference('Subscription 2021')
elif _payments['Invoice']['InvoiceNumber'].startswith(
'INV-20'):
updateInvoiceReference('Subscription 2020')
import requests
import utils
# https://github.com/CodeForeverAndEver/ColorIt
from colorit import *
# Use this to ensure that ColorIt will be usable by certain command line interfaces
init_colorit()
def filter_journal(_journals):
for _journal in _journals['Journals']:
print(f"\nSource Type: {_journal['SourceType']}")
for journal_line in _journal['JournalLines']:
print(f"Account Type: {journal_line['AccountType']} towards {journal_line['AccountName']} ({journal_line['AccountCode']}) the amount {journal_line['NetAmount']}")
return _journals
def save_journals(_journals):
print("Saved")
url = f"https://api.xero.com/api.xro/2.0/Journals"
# Get Invoices Created only this year
_header = {'If-Modified-Since': utils.year_start()}
journals = utils.xero_get(url,**_header)
filtered_journals = filter_journal(journals)
save_journals(filtered_journals)
print("DONE")
def get_member_txns(since_date):
print(color(f"\nProcessing Member Transactions\n================", Colors.blue))
_member_txns = {}
for bank_account in bank_accounts.items():
# Reset page counter for each account (DBS, NETS etc.)
has_more_pages = True
page = 0
# Go through pages (100 txns per page)
while has_more_pages:
page += 1
# This endpoint does not return payments applied to invoices, expense claims or transfers between bank accounts.
url = f'https://api.xero.com/api.xro/2.0/BankTransactions?where=BankAccount.Code=="{bank_account[1]}"&page={page}'
_header = {"If-Modified-Since": since_date}
txns = utils.xero_get(url, **_header)
if len(txns["BankTransactions"]) == 0:
has_more_pages = False
else:
print(color(f"Processing {bank_account[0]}. Page {page}", Colors.blue))
# Keep only "Type":"RECEIVE" & construct a dict via Python List Comprehension
_receive_txns = [
{
# Build the output item
"ContactID": _txn["Contact"]["ContactID"],
"ContactName": _txn["Contact"]["Name"],
"BankAccount": _txn["BankAccount"]["Name"],
# "Year": _txn['DateString'].split('-')[0],
"Year": str(utils.parse_Xero_Date(_txn["Date"]).year),
# Nested dict
"Line Items": _txn["LineItems"],
"Net Amount": _txn["Total"],
"Status": _txn["Status"],
}
for _txn in txns["BankTransactions"]
if (
# Only those tnxs that are payments to STOSC
_txn["Type"] == "RECEIVE"
and _txn["Status"] == "AUTHORISED"
and _txn["IsReconciled"] == True
)
]
receive_txns.extend(_receive_txns)
# Track accounts of interest
for _txn in txns["BankTransactions"]:
if (
# Only those tnxs that are payments to STOSC
_txn["Type"] == "RECEIVE"
and _txn["Status"] == "AUTHORISED"
and _txn["IsReconciled"] == True
):
# Build the member payments matrix
_receive_txns1 = {
# Build the output item
"ContactID": _txn["Contact"]["ContactID"],
"ContactName": _txn["Contact"]["Name"],
"BankAccount": _txn["BankAccount"]["Name"],
# "Year": _txn['DateString'].split('-')[0],
"Year": str(utils.parse_Xero_Date(_txn["Date"]).year),
# Nested dict
"Line Items": _txn["LineItems"],
"Net Amount": _txn["Total"],
"Status": _txn["Status"],
}
# Loop through each line item and see if it matches any "accounts of interest"
for lineItem in _txn["LineItems"]:
for account in accounts_of_interest:
if (
account["AccountCode"] == lineItem["AccountCode"]
and account["keyword"].upper() in lineItem["Description"].upper()
):
# Update Total
account['Total'] += Decimal(lineItem["LineAmount"])
account["modfied_ts"] = datetime.now().strftime("%d/%m/%Y %H:%M:%S")
return receive_txns
return None
contacts = open("contacts.txt", "r")
contacts = ['B030']
# For each member ID
for _contact in contacts1:
print(color(f"Processing {_contact[:4]}",Colors.white))
_contactID = get_ContactID(_contact[:4])
if _contactID:
# Get all Invoices for this contact
url = f"https://api.xero.com/api.xro/2.0/Invoices?ContactIDs={_contactID}&Statuses=AUTHORISED"
# Get Invoices Created only this year
_header = {'If-Modified-Since': utils.year_start()}
invoices = utils.xero_get(url,**_header)
for invoice in invoices['Invoices']:
print(color(f"Customer ID: {_contact}",Colors.purple))
# Only for FY 21 invoices
if invoice['InvoiceNumber'].startswith('INV-21'):
if invoice['Status'] == 'AUTHORISED':
# 01. Rename the invoice
new_invoice_data = {}
contact = {}
lineItems = {}
lineItems['LineItemID'] = invoice['LineItems'][0]['LineItemID']
lineItems['Description'] = "Subscription 2021"
lineItems['Quantity'] = 1.0
