def qbo_create_customer(sc):
client = create_qbc()
customer = Customer()
customer.GivenName = sc.first_name
customer.FamilyName = sc.last_name
customer.CompanyName = sc.company
phone = PhoneNumber()
phone.FreeFormNumber = sc.phone
customer.PrimaryPhone = phone
email = EmailAddress()
email.Address = sc.email
customer.PrimaryEmailAddr = email
address = Address()
address.Line1 = sc.address1
address.Line2 = sc.address2
address.City = sc.city
address.PostalCode = sc.post_code
customer.BillAddr = address
customer.save(qb=client)
return customer.Id
def test_update(self):
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
unique_name = datetime.now().strftime('%d%H%M%S')
customer.GivenName = unique_name
customer.save(qb=self.qb_client)
query_account = Customer.get(customer.Id, qb=self.qb_client)
self.assertEqual(query_account.GivenName, unique_name)
def test_to_ref(self):
customer = Customer()
customer.DisplayName = "test"
customer.Id = 100
ref = customer.to_ref()
self.assertEquals(ref.name, "test")
self.assertEquals(ref.type, "Customer")
self.assertEquals(ref.value, 100)
def test_update(self):
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
unique_name = datetime.now().strftime('%d%H%M%S')
customer.GivenName = unique_name
customer.save(qb=self.qb_client)
query_account = Customer.get(customer.Id, qb=self.qb_client)
self.assertEqual(query_account.GivenName, unique_name)
def create_customer(user, company, name):
client = get_qbo_client(user, company)
customer = Customer.filter(Active=True, DisplayName=name, qb=client)
if len(customer) == 0:
logger.debug("creating customer: " + name + " as " + user)
customer = Customer()
customer.DisplayName = name
customer = customer.save(qb=client)
logger.debug("customer saved")
else:
customer = customer[0]
return customer
def setUp(self):
self.qb = client.QuickBooks(sandbox=False,
consumer_key="consumer_key",
consumer_secret="consumer_secret",
access_token="access_token",
access_token_secret="access_token_secret",
company_id="company_id",
callback_url="callback_url",
verbose=True)
self.object1 = Customer()
self.object2 = Customer()
self.obj_list = [self.object1, self.object2]
def _set_location_in_creditnotes(self):
"""
Change Location value in creditnotes to Sales
contained in client's suffix field
TODO: make it scriptable to run into a cron job
"""
creditnotes = CreditMemo.filter(max_results=1000,
order_by="DocNumber DESC",
qb=self.client)
for cn in creditnotes:
if cn.DepartmentRef is None:
c = Customer.get(cn.CustomerRef.value, qb=self.client)
if c.Suffix != "":
location = self._get_location(sales_name=c.Suffix)
if location is not None:
# print(f"CREDIT NOTE {cn.DocNumber} ASSIGNED TO {c.Suffix} LOCATION ID {location.Id}")
cn.DepartmentRef = location.to_ref()
cn.save(qb=self.client)
else:
print(
f"[ERROR] Location IS NONE FOR {c.Suffix} OF {c}")
else:
print(f"[ERROR] c.Suffix IS NONE FOR {c}")
print(f"set_location_in_creditnotes ended")
def test_create(self):
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
item = Item.all(max_results=1, qb=self.qb_client)[0]
credit_memo = CreditMemo()
credit_memo.CustomerRef = customer.to_ref()
detail_line = SalesItemLine()
detail_line.LineNum = 1
detail_line.Description = "Test Description"
detail_line.Amount = 100
detail_line.DetailType = "SalesItemLineDetail"
detail_line.SalesItemLineDetail = SalesItemLineDetail()
detail_line.SalesItemLineDetail.ItemRef = item.to_ref()
credit_memo.Line.append(detail_line)
credit_memo.save(qb=self.qb_client)
query_credit_memo = CreditMemo.get(credit_memo.Id, qb=self.qb_client)
self.assertEquals(credit_memo.Id, query_credit_memo.Id)
self.assertEquals(query_credit_memo.CustomerRef.value, customer.Id)
line = query_credit_memo.Line[0]
self.assertEquals(line.LineNum, 1)
self.assertEquals(line.Description, "Test Description")
self.assertEquals(line.Amount, 100)
self.assertEquals(line.DetailType, "SalesItemLineDetail")
self.assertEquals(line.SalesItemLineDetail.ItemRef.value, item.Id)
def test_create(self):
invoice = Invoice()
line = SaleItemLine()
line.LineNum = 1
line.Description = "description"
line.Amount = 100
line.SalesItemLineDetail = SalesItemLineDetail()
item = Item.all(max_results=1, qb=self.qb_client)[0]
line.SalesItemLineDetail.ItemRef = item.to_ref()
invoice.Line.append(line)
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
invoice.CustomerRef = customer.to_ref()
invoice.CustomerMemo = CustomerMemo()
invoice.CustomerMemo.value = "Customer Memo"
invoice.save(qb=self.qb_client)
query_invoice = Invoice.get(invoice.Id, qb=self.qb_client)
self.assertEquals(query_invoice.CustomerRef.name, customer.DisplayName)
self.assertEquals(query_invoice.CustomerMemo.value, "Customer Memo")
self.assertEquals(query_invoice.Line[0].Description, "description")
self.assertEquals(query_invoice.Line[0].Amount, 100.0)
def create_qb_invoice(self, qb_client):
invoice = QuickBooksInvoice()
line = SalesItemLine()
line.LineNum = 1
line.Description = self.description
line.Amount = self.amount
# line.ServiceDate = qb_date_format(datetime.date(2019, 1, 1))
line.SalesItemLineDetail = SalesItemLineDetail()
line.SalesItemLineDetail.Qty = 1
line.SalesItemLineDetail.UnitPrice = self.amount
item = Item.choose(["MF"], field="SKU", qb=qb_client)[0]
line.SalesItemLineDetail.ItemRef = item.to_ref()
invoice.Line.append(line)
customer = Customer.get(self.organization.qbo_id, qb=qb_client)
invoice.CustomerRef = customer.to_ref()
# term = Term.choose(['Net 30'], field='Name', qb=qb_client)[0]
# invoice.SalesTermRef = term
# invoice.TotalAmt = self.amount
invoice.save(qb=qb_client)
print(invoice.Id)
def test_create(self):
invoice = Invoice()
line = SaleItemLine()
line.LineNum = 1
line.Description = "description"
line.Amount = 100
line.SalesItemLineDetail = SalesItemLineDetail()
item = Item.all(max_results=1, qb=self.qb_client)[0]
line.SalesItemLineDetail.ItemRef = item.to_ref()
invoice.Line.append(line)
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
invoice.CustomerRef = customer.to_ref()
invoice.CustomerMemo = CustomerMemo()
invoice.CustomerMemo.value = "Customer Memo"
invoice.save(qb=self.qb_client)
query_invoice = Invoice.get(invoice.Id, qb=self.qb_client)
self.assertEquals(query_invoice.CustomerRef.name, customer.DisplayName)
self.assertEquals(query_invoice.CustomerMemo.value, "Customer Memo")
self.assertEquals(query_invoice.Line[0].Description, "description")
self.assertEquals(query_invoice.Line[0].Amount, 100.0)
def test_delete(self):
# First create an invoice
invoice = Invoice()
line = SalesItemLine()
line.LineNum = 1
line.Description = "description"
line.Amount = 100
line.SalesItemLineDetail = SalesItemLineDetail()
item = Item.all(max_results=1, qb=self.qb_client)[0]
line.SalesItemLineDetail.ItemRef = item.to_ref()
invoice.Line.append(line)
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
invoice.CustomerRef = customer.to_ref()
invoice.CustomerMemo = CustomerMemo()
invoice.CustomerMemo.value = "Customer Memo"
invoice.save(qb=self.qb_client)
# Then delete
invoice_id = invoice.Id
invoice.delete(qb=self.qb_client)
query_invoice = Invoice.filter(Id=invoice_id, qb=self.qb_client)
self.assertEqual([], query_invoice)
def test_create(self):
customer = Customer.all(max_results=1)[0]
item = Item.all(max_results=1)[0]
credit_memo = CreditMemo()
credit_memo.CustomerRef = customer.to_ref()
detail_line = CreditMemoLine()
detail_line.LineNum = 1
detail_line.Description = "Test Description"
detail_line.Amount = 100
detail_line.DetailType = "SalesItemLineDetail"
detail_line.SalesItemLineDetail = SalesItemLineDetail()
detail_line.SalesItemLineDetail.ItemRef = item.to_ref()
credit_memo.Line.append(detail_line)
credit_memo.save()
query_credit_memo = CreditMemo.get(credit_memo.Id)
self.assertEquals(credit_memo.Id, query_credit_memo.Id)
self.assertEquals(query_credit_memo.CustomerRef.value, customer.Id)
line = query_credit_memo.Line[0]
self.assertEquals(line.LineNum, 1)
self.assertEquals(line.Description, "Test Description")
self.assertEquals(line.Amount, 100)
self.assertEquals(line.DetailType, "SalesItemLineDetail")
self.assertEquals(line.SalesItemLineDetail.ItemRef.value, item.Id)
def qbo_check_customer(sc):
client = create_qbc()
customers = Customer.filter(Active=True, FamilyName=sc.last_name, GivenName=sc.first_name, qb=client)
if(len(customers) == 0):
return qbo_create_customer(sc)
else:
return customers[0].Id
def setUp(self):
self.qb_client = QuickBooks(
sandbox=True,
consumer_key=os.environ.get('CONSUMER_KEY'),
consumer_secret=os.environ.get('CONSUMER_SECRET'),
access_token=os.environ.get('ACCESS_TOKEN'),
access_token_secret=os.environ.get('ACCESS_TOKEN_SECRET'),
company_id=os.environ.get('COMPANY_ID')
)
self.customer = Customer.all(max_results=1, qb=self.qb_client)[0]
def test_create(self):
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
taxcode = TaxCode.all(max_results=1, qb=self.qb_client)[0]
item = Item.filter(Type='Inventory', max_results=1,
qb=self.qb_client)[0]
vendor = Vendor.all(max_results=1, qb=self.qb_client)[0]
account = Account.all(max_results=1, qb=self.qb_client)[0]
purchaseorder = PurchaseOrder()
detail_line = ItemBasedExpenseLine()
detail_line.Amount = 100
detail_line.ItemBasedExpenseLineDetail = ItemBasedExpenseLineDetail()
detail_line.ItemBasedExpenseLineDetail.BillableStatus = "NotBillable"
detail_line.ItemBasedExpenseLineDetail.UnitPrice = 100
detail_line.ItemBasedExpenseLineDetail.Qty = 1
detail_line.ItemBasedExpenseLineDetail.CustomerRef = customer.to_ref()
detail_line.ItemBasedExpenseLineDetail.TaxCodeRef = taxcode.to_ref()
detail_line.ItemBasedExpenseLineDetail.ItemRef = item.to_ref()
purchaseorder.Line.append(detail_line)
purchaseorder.VendorRef = vendor.to_ref()
purchaseorder.APAccountRef = account.to_ref()
purchaseorder.TotalAmt = 100
#print purchaseorder.to_json()
purchaseorder.save(qb=self.qb_client)
query_purchaseorder = PurchaseOrder.get(purchaseorder.Id,
qb=self.qb_client)
self.assertEqual(query_purchaseorder.VendorRef.value, vendor.Id)
self.assertEqual(query_purchaseorder.APAccountRef.value, account.Id)
self.assertEqual(query_purchaseorder.TotalAmt, 100)
query_detail_line = query_purchaseorder.Line[0]
self.assertEqual(query_detail_line.Amount, 100)
self.assertEqual(
query_detail_line.ItemBasedExpenseLineDetail.UnitPrice, 100)
self.assertEqual(query_detail_line.ItemBasedExpenseLineDetail.Qty, 1)
self.assertEqual(
query_detail_line.ItemBasedExpenseLineDetail.CustomerRef.value,
customer.Id)
self.assertEqual(
query_detail_line.ItemBasedExpenseLineDetail.TaxCodeRef.value,
taxcode.Name)
self.assertEqual(
query_detail_line.ItemBasedExpenseLineDetail.ItemRef.value,
item.Id)
def importUsersFromQuickbooks(request):
client = get_qb_client()
customers = Customer.all(qb=client)
for customer in customers:
if customer.PrimaryEmailAddr:
emails = customer.PrimaryEmailAddr.Address
if ',' in emails:
for email in emails.split(","):
if email:
email = email.strip()
else:
continue
if not User.objects.filter(email=email).exists():
password = User.objects.make_random_password(length=8,
allowed_chars='abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789')
created = User.objects.create_user(
first_name=customer.GivenName,
last_name=customer.FamilyName,
email=email,
qb_customer_id=customer.Id,
)
# TODO: Email raw password to the user on account creation
# so they can change it themselves.
created.set_password(password)
created.save()
else:
email = emails
if not User.objects.filter(email=email).exists():
password = User.objects.make_random_password(length=8,
allowed_chars='abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789')
created = User.objects.create_user(
first_name=customer.GivenName,
last_name=customer.FamilyName,
email=email,
qb_customer_id=customer.Id,
)
# TODO: Email raw password to the user on account creation
# so they can change it themselves.
created.set_password(password)
created.save()
else:
continue
return render(request, 'management.html')
def qbo_callback():
# https://github.com/sidecars/python-quickbooks
from quickbooks import Oauth2SessionManager
from quickbooks import QuickBooks
error = request.args.get('error', '')
if error:
return "Error: " + error
state = request.args.get('state', '')
if not is_valid_state(state):
# Uh-oh, this request wasn't started by us!
abort(403)
authorization_code = request.args.get('code')
realm_id = request.args.get('realmId')
session_manager = Oauth2SessionManager(
client_id=CLIENT_ID,
client_secret=CLIENT_SECRET,
base_url='http://localhost:8000/qbo_callback',
)
session_manager.get_access_tokens(authorization_code)
access_token = session_manager.access_token
session_manager = Oauth2SessionManager(
client_id=realm_id,
client_secret=CLIENT_SECRET,
access_token=access_token,
)
client = QuickBooks(
sandbox=True,
session_manager=session_manager,
company_id=realm_id
)
from quickbooks.objects.customer import Customer
customers = Customer.all(qb=client)
import_googlesheets(customers)
client.disconnect_account()
# Note: In most cases, you'll want to store the access token, in, say,
# a session for use in other parts of your web app.
return "Your qbo authorization_code is %s realm_id is: %s customers: %s" % (authorization_code, realm_id, customers)
def __init__(self):
"""
Init function
Create a client for the API and load all the clients
to speed up the retrival process.
TODO now it loads only 1000 clients, extend to all
"""
super(PythonQuickBooks, self).__init__()
self.client = self._create_client()
self.customers = Customer.where("Active=True",
order_by='DisplayName',
max_results=1000,
qb=self.client)
self.trovati = 0
self.quanti = len(self.customers)
def verify_invoice(doc_number="", email=""):
# checks QBO to ensure invoice number matches email provided
# if match, returns QBO customer object attached to invoice
# if mismatch, returns None
refresh_stored_tokens()
qb = fetch('qbclient')
invoice_list = Invoice.filter(DocNumber=doc_number, qb=qb)
if invoice_list:
customers = Customer.filter(id=invoice_list[0].CustomerRef.value,
qb=qb)
else:
return None
if customers:
if customers[0].PrimaryEmailAddr.Address.lower() == email.lower():
return customers[0]
else:
return None
else:
return None
def test_create(self):
customer = Customer.all(max_results=1, qb=self.qb_client)[0]
taxcode = TaxCode.all(max_results=1, qb=self.qb_client)[0]
item = Item.filter(Type='Inventory', max_results=1, qb=self.qb_client)[0]
vendor = Vendor.all(max_results=1, qb=self.qb_client)[0]
account = Account.all(max_results=1, qb=self.qb_client)[0]
purchaseorder = PurchaseOrder()
detail_line = ItemBasedExpenseLine()
detail_line.Amount = 100
detail_line.ItemBasedExpenseLineDetail = ItemBasedExpenseLineDetail()
detail_line.ItemBasedExpenseLineDetail.BillableStatus = "NotBillable"
detail_line.ItemBasedExpenseLineDetail.UnitPrice = 100
detail_line.ItemBasedExpenseLineDetail.Qty = 1
detail_line.ItemBasedExpenseLineDetail.CustomerRef = customer.to_ref()
detail_line.ItemBasedExpenseLineDetail.TaxCodeRef = taxcode.to_ref()
detail_line.ItemBasedExpenseLineDetail.ItemRef = item.to_ref()
purchaseorder.Line.append(detail_line)
purchaseorder.VendorRef = vendor.to_ref()
purchaseorder.APAccountRef = account.to_ref()
purchaseorder.TotalAmt = 100
print purchaseorder.to_json()
purchaseorder.save(qb=self.qb_client)
query_purchaseorder = PurchaseOrder.get(purchaseorder.Id, qb=self.qb_client)
self.assertEquals(query_purchaseorder.VendorRef.value, vendor.Id)
self.assertEquals(query_purchaseorder.APAccountRef.value, account.Id)
self.assertEquals(query_purchaseorder.TotalAmt, 100)
query_detail_line = query_purchaseorder.Line[0]
self.assertEquals(query_detail_line.Amount, 100)
self.assertEquals(query_detail_line.ItemBasedExpenseLineDetail.UnitPrice, 100)
self.assertEquals(query_detail_line.ItemBasedExpenseLineDetail.Qty, 1)
self.assertEquals(query_detail_line.ItemBasedExpenseLineDetail.CustomerRef.value, customer.Id)
self.assertEquals(query_detail_line.ItemBasedExpenseLineDetail.TaxCodeRef.value, taxcode.Name)
self.assertEquals(query_detail_line.ItemBasedExpenseLineDetail.ItemRef.value, item.Id)
def create_customer(sr):
# Get the customer
customer = Customer()
customer_body = {
"GivenName": sr.customer_first.capitalize(),
"FamilyName": sr.customer_last.capitalize(),
"FullyQualifiedName": sr.customer_name,
"PrimaryEmailAddr": {
"Address": sr.customer_email
},
"DisplayName": sr.customer_name,
#"Suffix": "Jr",
#"Title": "Mr",
#"MiddleName": "B",
"Notes": sr.memo,
"PrimaryPhone": {
"FreeFormNumber": sr.customer_phone
},
#"CompanyName": "King Groceries",
"BillAddr": {
"CountrySubDivisionCode": sr.customer_state,
"City": sr.customer_city,
"PostalCode": sr.customer_zip,
"Line1": sr.customer_street,
"Country": "USA"
},
"ShipAddr": {
"CountrySubDivisionCode": sr.customer_state,
"City": sr.customer_city,
"PostalCode": sr.customer_zip,
"Line1": sr.customer_street,
"Country": "USA"
}
}
customer = customer.from_json(customer_body)
#revise customer here
logging.debug("Customer Body Sent: {}".format(customer_body))
try:
customer.save(qb_client)
logging.debug("New Customer Info: {}".format(customer.to_json()))
return customer
except QuickbooksException as e:
logging.error("Errot saving new customer. [{}]".format(e.detail))
return None
def qbo_create_invoice(so, customer_id):
client = create_qbc()
customer_ref = Customer.get(customer_id, qb=client).to_ref()
line_detail = SalesItemLineDetail()
line_detail.UnitPrice = 100 # in dollars
line_detail.Qty = 1 # quantity can be decimal
item_ref = Item.get(35, qb=client).to_ref()
line_detail.ItemRef = item_ref
line = SalesItemLine()
line.Amount = 100 # in dollars
line.SalesItemLineDetail = line_detail
line.DetailType = "SalesItemLineDetail"
invoice = Invoice()
invoice.CustomerRef = customer_ref
invoice.Line = [line]
invoice.save(qb=client)
def test_create(self):
customer = Customer()
customer.Title = self.title
customer.GivenName = self.given_name
customer.MiddleName = self.middle_name
customer.FamilyName = self.family_name
customer.Suffix = self.suffix
customer.FullyQualifiedName = self.fully_qualified_name
customer.CompanyName = self.company_name
customer.DisplayName = self.display_name
customer.BillAddr = Address()
customer.BillAddr.Line1 = "123 Main"
customer.BillAddr.Line2 = "Apartment 1"
customer.BillAddr.City = "City"
customer.BillAddr.Country = "U.S.A"
customer.BillAddr.CountrySubDivisionCode = "CA"
customer.BillAddr.PostalCode = "94030"
customer.PrimaryPhone = PhoneNumber()
customer.PrimaryPhone.FreeFormNumber = '555-555-5555'
customer.PrimaryEmailAddr = EmailAddress()
customer.PrimaryEmailAddr.Address = '*****@*****.**'
customer.save(qb=self.qb_client)
query_customer = Customer.get(customer.Id, qb=self.qb_client)
self.assertEquals(customer.Id, query_customer.Id)
self.assertEqual(query_customer.Title, self.title)
self.assertEqual(query_customer.GivenName, self.given_name)
self.assertEqual(query_customer.MiddleName, self.middle_name)
self.assertEqual(query_customer.FamilyName, self.family_name)
self.assertEqual(query_customer.Suffix, self.suffix)
self.assertEqual(query_customer.FullyQualifiedName, self.fully_qualified_name)
self.assertEqual(query_customer.CompanyName, self.company_name)
self.assertEqual(query_customer.DisplayName, self.display_name)
self.assertEqual(query_customer.BillAddr.Line1, customer.BillAddr.Line1)
self.assertEqual(query_customer.BillAddr.Line2, customer.BillAddr.Line2)
self.assertEqual(query_customer.BillAddr.City, customer.BillAddr.City)
self.assertEqual(query_customer.BillAddr.Country, customer.BillAddr.Country)
self.assertEqual(query_customer.BillAddr.CountrySubDivisionCode, customer.BillAddr.CountrySubDivisionCode)
self.assertEqual(query_customer.BillAddr.PostalCode, customer.BillAddr.PostalCode)
self.assertEqual(query_customer.PrimaryPhone.FreeFormNumber, customer.PrimaryPhone.FreeFormNumber)
self.assertEqual(query_customer.PrimaryEmailAddr.Address, customer.PrimaryEmailAddr.Address)
def sync_dues(self, request):
"""
This will sync with quickbooks
"""
client = get_quickbooks_client()
chapter_name = self.name
if "Chapter" in chapter_name:
chapter_name = chapter_name.replace(" Chapter", "")
customer = Customer.query(
select=f"SELECT * FROM Customer WHERE CompanyName LIKE '{chapter_name} chapter%'",
qb=client,
)
if customer:
customer = customer[0]
else:
messages.add_message(
request,
messages.ERROR,
f"Quickbooks Customer matching name: '{chapter_name} Chapter...' not found",
)
return
invoice, linenumber_count = invoice_search("1", customer, client)
count = self.active_actives().count()
if not self.candidate_chapter:
# D1; Service; Semiannual Chapter Dues payable @ $80 each # Minimum per chapter is $1600.
line = create_line(
count, linenumber_count, name="D1", minimum=1600, client=client
)
l1_min = 250
if self.house:
l1_min = 1125
else:
# D2; Service; Semiannual Colony Dues
line = create_line(count, linenumber_count, name="D2", client=client)
l1_min = 125
linenumber_count += 1
invoice.Line.append(line)
# L1; Service; Health and Safety Assessment - Semesterly
# minimum for housed chapters ($1125)
# unhoused chapters ($250)
# Colony Minimum is $125
line = create_line(
count, linenumber_count, name="L1", minimum=l1_min, client=client
)
linenumber_count += 1
invoice.Line.append(line)
if self.health_safety_surcharge != "none":
line = create_line(
line.Amount,
linenumber_count,
name=self.health_safety_surcharge,
client=client,
)
invoice.Line.append(line)
memo = f"Actives: {count}; Surcharge: {self.SURCHARGE.get_value(self.health_safety_surcharge)}"
memo = memo[0:999]
invoice.CustomerMemo.value = memo
invoice.DeliveryInfo = None
invoice_obj = invoice.save(qb=client)
attachment_path = self.generate_dues_attachment(file_obj=True)
attachment = Attachable()
attachable_ref = AttachableRef()
attachable_ref.EntityRef = invoice.to_ref()
attachable_ref.IncludeOnSend = True
attachment.AttachableRef.append(attachable_ref)
attachment.FileName = attachment_path.name
attachment._FilePath = str(attachment_path.absolute())
attachment.ContentType = "text/csv"
attachment.save(qb=client)
if attachment_path.exists():
attachment_path.unlink() # Delete the file when we are done
return invoice_obj.DocNumber
from models import Customer
from quickbooks.objects.customer import Customer as QbCustomer
from qbclient import client
customersLeft = True
totalCustomers = []
startPosition = 1
maxResults = 1000
page = 1
qbLastUpdate = None
while customersLeft:
startPosition = (page - 1) * maxResults + 1
customers = QbCustomer.query("SELECT * FROM Customer STARTPOSITION {0} MAXRESULTS {1}".format(startPosition, maxResults),qb=client)
totalCustomers.extend(customers)
page += 1
if len(customers) < 1000:
customersLeft = False
for customer in totalCustomers:
newCustomer = Customer()
newCustomer.from_quickbooks(customer)
newCustomer.save()
def test_valid_object_name(self):
obj = Customer()
client = QuickBooks()
result = client.isvalid_object_name(obj.qbo_object_name)
self.assertTrue(result)
def test_create(self):
self.customer = Customer.all(max_results=1, qb=self.qb_client)[0]
estimate = Estimate()
estimate.TotalAmt = 31.5
estimate.ApplyTaxAfterDiscount = False
estimate.PrintStatus = "NeedToPrint"
estimate.EmailStatus = "NotSet"
estimate.BillAddr = Address()
estimate.BillAddr.Line1 = "65 Ocean Dr."
estimate.BillAddr.City = "Half Moon Bay"
estimate.BillAddr.CountrySubDivisionCode = "CA"
estimate.BillAddr.PostalCode = "94213"
estimate.BillAddr.Lat = "37.4300318"
estimate.BillAddr.Long = "-122.4336537"
estimate.ShipAddr = Address()
estimate.ShipAddr.Id = "2" + datetime.now().strftime('%d%H%M')
estimate.ShipAddr.Line1 = "65 Ocean Dr."
estimate.ShipAddr.City = "Half Moon Bay"
estimate.ShipAddr.CountrySubDivisionCode = "CA"
estimate.ShipAddr.PostalCode = "94213"
estimate.ShipAddr.Lat = "37.4300318"
estimate.ShipAddr.Long = "-122.4336537"
estimate.BillEmail = EmailAddress()
estimate.BillEmail.Address = "*****@*****.**"
estimate.CustomerMemo = CustomerMemo()
estimate.CustomerMemo.value = "Thank you for your business and have a great day!"
estimate.CustomerRef = Ref()
estimate.CustomerRef.value = self.customer.Id
estimate.CustomerRef.name = self.customer.DisplayName
estimate.TxnTaxDetail = TxnTaxDetail()
estimate.TxnTaxDetail.TotalTax = 0
line = SalesItemLine()
line.LineNum = 1
line.Description = "Pest Control Services"
line.Amount = 35.0
line.SalesItemLineDetail = SalesItemLineDetail()
line.SalesItemLineDetail.UnitPrice = 35
line.SalesItemLineDetail.Qty = 1
item_ref = Ref()
item_ref.value = "10"
item_ref.name = "Pest Control"
line.SalesItemLineDetail.ItemRef = item_ref
tax_code_ref = Ref()
tax_code_ref.value = "NON"
line.SalesItemLineDetail.TaxCodeRef = tax_code_ref
estimate.Line.append(line)
line2 = DiscountLine()
line2.Amount = 3.5
line2.DiscountLineDetail = DiscountLineDetail()
line2.DiscountLineDetail.PercentBased = True
line2.DiscountLineDetail.DiscountPercent = 10
line2.DiscountLineDetail.DiscountAccountRef = Ref()
line2.DiscountLineDetail.DiscountAccountRef.value = "86"
line2.DiscountLineDetail.DiscountAccountRef.name = "Discounts given"
line2.DetailType = "DiscountLineDetail"
estimate.Line.append(line2)
estimate.save(qb=self.qb_client)
query_estimate = Estimate.get(estimate.Id, qb=self.qb_client)
self.assertEqual(query_estimate.Id, estimate.Id)
self.assertEqual(query_estimate.TotalAmt, estimate.TotalAmt)
self.assertEqual(query_estimate.ApplyTaxAfterDiscount,
estimate.ApplyTaxAfterDiscount)
self.assertEqual(query_estimate.PrintStatus, estimate.PrintStatus)
self.assertEqual(query_estimate.EmailStatus, estimate.EmailStatus)
self.assertEqual(query_estimate.BillAddr.Line1,
estimate.BillAddr.Line1)
self.assertEqual(query_estimate.BillAddr.City, estimate.BillAddr.City)
self.assertEqual(query_estimate.BillAddr.CountrySubDivisionCode,
estimate.BillAddr.CountrySubDivisionCode)
self.assertEqual(query_estimate.BillAddr.PostalCode,
estimate.BillAddr.PostalCode)
self.assertEqual(query_estimate.ShipAddr.Line1,
estimate.ShipAddr.Line1)
self.assertEqual(query_estimate.ShipAddr.City, estimate.ShipAddr.City)
self.assertEqual(query_estimate.ShipAddr.CountrySubDivisionCode,
estimate.ShipAddr.CountrySubDivisionCode)
self.assertEqual(query_estimate.ShipAddr.PostalCode,
estimate.ShipAddr.PostalCode)
self.assertEqual(query_estimate.BillEmail.Address,
estimate.BillEmail.Address)
self.assertEqual(query_estimate.CustomerMemo.value,
estimate.CustomerMemo.value)
self.assertEqual(query_estimate.CustomerRef.value,
estimate.CustomerRef.value)
self.assertEqual(query_estimate.CustomerRef.name,
estimate.CustomerRef.name)
self.assertEqual(query_estimate.TxnTaxDetail.TotalTax,
estimate.TxnTaxDetail.TotalTax)
self.assertEqual(query_estimate.Line[0].LineNum,
estimate.Line[0].LineNum)
self.assertEqual(query_estimate.Line[0].Description,
estimate.Line[0].Description)
self.assertEqual(query_estimate.Line[0].Amount,
estimate.Line[0].Amount)
self.assertEqual(query_estimate.Line[0].SalesItemLineDetail.UnitPrice,
estimate.Line[0].SalesItemLineDetail.UnitPrice)
self.assertEqual(query_estimate.Line[0].SalesItemLineDetail.Qty,
estimate.Line[0].SalesItemLineDetail.Qty)
self.assertEqual(query_estimate.Line[2].Amount,
estimate.Line[1].Amount)
self.assertEqual(
query_estimate.Line[2].DiscountLineDetail.PercentBased,
estimate.Line[1].DiscountLineDetail.PercentBased)
self.assertEqual(
query_estimate.Line[2].DiscountLineDetail.DiscountPercent,
estimate.Line[1].DiscountLineDetail.DiscountPercent)
self.assertEqual(
query_estimate.Line[2].DiscountLineDetail.DiscountAccountRef.value,
estimate.Line[1].DiscountLineDetail.DiscountAccountRef.value)
self.assertEqual(
query_estimate.Line[2].DiscountLineDetail.DiscountAccountRef.name,
estimate.Line[1].DiscountLineDetail.DiscountAccountRef.name)
# Your app needs an OAuth 2.0 Access Token to access QuickBooks Online data. The OAuth 2.0 playground is the easiest way to get your access token.
# https://developer.intuit.com/app/developer/playground
# CAN GET THE AUTH TOKEN FOR EITHER SANDBOX OR PRODUCTION APP!!!
# ALSO, this test API page lets you try things out...
# https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/account
# https://developer.intuit.com/app/developer/qbo/docs/get-started
# By now, you have your Client ID, Client Secret, and OAuth access token. Next, you can try making an API call. In the snippet below:
from quickbooks import QuickBooks
client = QuickBooks(
auth_client=auth_client,
refresh_token=oauth2_token_from_auth['refreshToken'], #'REFRESH_TOKEN',
company_id=realm_id, #'COMPANY_ID',
# minorversion=4
)
from quickbooks.objects.customer import Customer
customers = Customer.all(qb=client)
# Note: The maximum number of entities that can be returned in a response is 1000. If the result size is not specified, the default number is 100. (See Intuit developer guide for details)
print("####################\nShow CUSTOMER NAMES\n####################")
for (cnt, a) in enumerate(customers):
print(cnt, a.DisplayName)
from quickbooks.objects.account import Account
accounts = Account.all(qb=client)
print("####################\nShow ACCOUNT NAMES\n####################")
for (cnt, a) in enumerate(accounts):
print(cnt, a.Name)
def test_order_by(self, query):
Customer.filter(Active=True, order_by='DisplayName')
query.assert_called_once_with(
"SELECT * FROM Customer WHERE Active = True ORDERBY DisplayName",
qb=None)
def test_create(self):
customer = Customer()
customer.Title = self.title
customer.GivenName = self.given_name
customer.MiddleName = self.middle_name
customer.FamilyName = self.family_name
customer.Suffix = self.suffix
customer.FullyQualifiedName = self.fully_qualified_name
customer.CompanyName = self.company_name
customer.DisplayName = self.display_name
customer.BillAddr = Address()
customer.BillAddr.Line1 = "123 Main"
customer.BillAddr.Line2 = "Apartment 1"
customer.BillAddr.City = "City"
customer.BillAddr.Country = "U.S.A"
customer.BillAddr.CountrySubDivisionCode = "CA"
customer.BillAddr.PostalCode = "94030"
customer.PrimaryPhone = PhoneNumber()
customer.PrimaryPhone.FreeFormNumber = '555-555-5555'
customer.PrimaryEmailAddr = EmailAddress()
customer.PrimaryEmailAddr.Address = '*****@*****.**'
customer.save(qb=self.qb_client)
query_customer = Customer.get(customer.Id, qb=self.qb_client)
self.assertEquals(customer.Id, query_customer.Id)
self.assertEqual(query_customer.Title, self.title)
self.assertEqual(query_customer.GivenName, self.given_name)
self.assertEqual(query_customer.MiddleName, self.middle_name)
self.assertEqual(query_customer.FamilyName, self.family_name)
self.assertEqual(query_customer.Suffix, self.suffix)
self.assertEqual(query_customer.FullyQualifiedName,
self.fully_qualified_name)
self.assertEqual(query_customer.CompanyName, self.company_name)
self.assertEqual(query_customer.DisplayName, self.display_name)
self.assertEqual(query_customer.BillAddr.Line1,
customer.BillAddr.Line1)
self.assertEqual(query_customer.BillAddr.Line2,
customer.BillAddr.Line2)
self.assertEqual(query_customer.BillAddr.City, customer.BillAddr.City)
self.assertEqual(query_customer.BillAddr.Country,
customer.BillAddr.Country)
self.assertEqual(query_customer.BillAddr.CountrySubDivisionCode,
customer.BillAddr.CountrySubDivisionCode)
self.assertEqual(query_customer.BillAddr.PostalCode,
customer.BillAddr.PostalCode)
self.assertEqual(query_customer.PrimaryPhone.FreeFormNumber,
customer.PrimaryPhone.FreeFormNumber)
self.assertEqual(query_customer.PrimaryEmailAddr.Address,
customer.PrimaryEmailAddr.Address)
def test_unicode(self):
customer = Customer()
customer.DisplayName = "test"
self.assertEquals(str(customer), "test")
def handle(self, *args, **options):
live = options.get("live", False)
print(f"This is LIVE: ", live)
Invoice.objects.all().delete()
client = get_quickbooks_client()
customers = Customer.all(qb=client, max_results=1000)
for customer in customers:
chapter_name = customer.CompanyName
if not chapter_name or not hasattr(customer, "CustomerTypeRef"):
continue
customer_type = customer.CustomerTypeRef["value"]
if customer_type == "7300000000000214210":
# This is a chapter
if "Chapter" in chapter_name:
chapter_name = customer.CompanyName.split(" Chapter")[0]
elif customer_type == "7300000000000214211":
# This is a candidate chapter
# Candidate Chapter is normally in the name
pass
elif customer_type == "7300000000000220483":
# This is a natoff
continue
elif customer_type == "7300000000000247061":
# This is other
continue
else:
# Maybe not chapter/candidate chapter, but other?
continue
print(f"Syncing: ", chapter_name)
try:
chapter = Chapter.objects.get(name=chapter_name)
except Chapter.DoesNotExist:
print(f" Chapter matching {chapter_name} does not exist")
continue
balance = customer.Balance
print(" New balance: ", balance)
if live:
chapter.balance = balance
chapter.balance_date = timezone.now()
chapter.save()
# Total emails are limited to 100 characters, need to be strategic
# [regent, scribe, vice, treasurer]
council_emails = chapter.get_current_officers_council_specific()
# [email_regent, email_scribe, email_vice_regent, email_treasurer, email_corresponding_secretary, email,
generic_emails = chapter.get_generic_chapter_emails()
emails = [
# Tresurer
council_emails[3],
generic_emails[3],
# Generic
generic_emails[5],
# Regent
council_emails[0],
generic_emails[0],
# Vice
council_emails[2],
generic_emails[2],
# Scribe
council_emails[1],
generic_emails[1],
# Corsec
generic_emails[4],
]
emails = [email for email in emails if email]
if not emails:
print(" NO EMAILS")
email_str = ""
for email in emails:
if not isinstance(email, str):
email = email.email
if not email:
continue
if (len(email_str + email) +
1) < 100 and email not in email_str:
email_str = email_str + email + ","
else:
break
email_str = email_str[:-1]
print(" Current Email: ", customer.PrimaryEmailAddr.Address)
if customer.PrimaryEmailAddr.Address != email_str:
print(" New Email: ", email_str)
if live:
customer.PrimaryEmailAddr.Address = email_str
customer.save(qb=client)
else:
print(" No new emails")
if not balance > 0:
continue
invoices = QBInvoice.query(
select=
f"select * from Invoice where balance > '0' AND CustomerRef = '{customer.Id}'",
qb=client,
)
for invoice_res in invoices:
invoice = QBInvoice.get(invoice_res.Id, qb=client)
Invoice(
link=invoice.InvoiceLink,
due_date=invoice.DueDate,
central_id=invoice.DocNumber,
description="<br>".join([
f"{line.Description}; Line Amount: {line.Amount} <br>"
for line in invoice.Line
if line.DetailType == "SalesItemLineDetail"
]),
total=invoice.Balance,
chapter=chapter,
).save()
def test_order_by_with_qb(self):
with patch.object(self.qb_client, 'query') as query:
Customer.filter(Active=True,
order_by='DisplayName',
qb=self.qb_client)
self.assertTrue(query.called)
def index(self):
'''
Load all customers and 100 invoices and the correlate them.
Customers get put into three bins: Ready to invoice, current (no need for an invoice) and WTF (we couldn't sort what should happen).
Send all this to the caller to render.
'''
# Look up access tokens from sessions, or make the user login
access_token = session.get('access_token', None)
refresh_token = session.get('refresh_token', "")
realm = session.get('realm', None)
if not access_token or not refresh_token:
session['realm'] = realm
session['access_token'] = access_token
session['refresh_token'] = refresh_token
return render_template("quickbooks/login.html")
refreshed = False
while True:
# Now fetch customers and invoices
try:
client = get_client(realm, refresh_token)
customers = Customer.filter(Active=True, qb=client)
invoices = Invoice.query(
"select * from invoice order by metadata.createtime desc maxresults 300",
qb=client)
break
except quickbooks.exceptions.AuthorizationException as err:
current_app.logger.error("Auth failed. Refresh token: '%s'" %
client.refresh_token)
if not refreshed:
current_app.logger.debug("Auth failed, trying refresh")
refreshed = True
current_app.quickbooks_auth_client.refresh()
continue
flash("Authorization failed, please try again: %s" % err)
current_app.logger.debug(
"Auth failed, logging out, starting over.")
session['access_token'] = None
return redirect(url_for("quickbooks/.index"))
except quickbooks.exceptions.QuickbooksException as err:
flash("Query failed: %s" % err)
raise InternalServerError
# Calculate a pile of dates, based on today date. Figure out
# which quarter we're in, and the dates of this and 2 prior quarters
dt = datetime.datetime.now()
today = dt.strftime("%m-%d-%Y")
q = (dt.month - 1) // 3
pq = (q + 3) % 4
ppq = (pq + 3) % 4
year = dt.year
(q_start, q_end) = self.calculate_quarter_dates(year, q)
if pq > q:
year -= 1
(pq_start, pq_end) = self.calculate_quarter_dates(year, pq)
if ppq > pq:
year -= 1
(ppq_start, ppq_end) = self.calculate_quarter_dates(year, ppq)
# Iterate over all the invoices, parse their dates and arrange them into the invoice dict, by customer
invoice_dict = {}
for invoice in invoices:
customer_id = invoice.CustomerRef.value
if customer_id not in invoice_dict:
invoice_dict[customer_id] = []
create_time = parse(invoice.TxnDate).strftime("%m-%d-%Y")
try:
begin_dt = parse(invoice.CustomField[1].StringValue)
begin_date = begin_dt.strftime("%m-%d-%Y")
except ValueError:
begin_date = ""
begin_dt = None
try:
end_dt = parse(invoice.CustomField[2].StringValue)
end_date = end_dt.strftime("%m-%d-%Y")
except ValueError:
end_date = ""
end_dt = None
try:
tier = invoice.Line[0].SalesItemLineDetail.ItemRef.name
except AttributeError:
tier = ""
invoice_dict[customer_id].append({
'customer':
customer_id,
'date':
create_time,
'sortdate':
invoice.TxnDate,
'id':
invoice.Id,
'amount':
invoice.TotalAmt,
'begin':
begin_date,
'begin_dt':
begin_dt,
'end':
end_date,
'end_dt':
end_dt,
'service':
tier,
'number':
invoice.DocNumber,
'currency':
invoice.CurrencyRef.value,
'qty':
invoice.Line[0].SalesItemLineDetail.Qty,
'price':
invoice.Line[0].SalesItemLineDetail.UnitPrice
})
# Finally, classify customers into the three bins
ready_to_invoice = []
wtf = []
current = []
for customer in customers:
invoices = invoice_dict.get(customer.Id, [])
invoices = sorted(invoices,
key=lambda invoice: invoice['sortdate'],
reverse=True)
# If there are comments in the customer notes field that indicates # arrears or # donotinvoice,
# we use those as hints to properly create new invoices or to ignore customers
name = customer.DisplayName or customer.CompanyName
desc = customer.Notes.lower()
try:
price = invoices[0]['price']
except IndexError:
price = 0
if desc.find("arrears") >= 0:
name += " (arrears)"
is_arrears = True
else:
is_arrears = False
if desc.find("donotinvoice") >= 0:
do_not_invoice = True
name += " (do not invoice)"
else:
do_not_invoice = False
# create the customer object, ready for saving
cust = {'name': name, 'invoices': invoices, 'id': customer.Id}
if do_not_invoice:
current.append(cust)
continue
# If there are no previous invoices, go WTF!
if not invoices:
wtf.append(cust)
continue
# If this customer should not be invoiced or if the last invoice corresponds to this quarter,
# place them into the current bin
if do_not_invoice or (invoices[0]['begin'] == q_start
and invoices[0]['end'] == q_end):
current.append(cust)
continue
# If the customer is not invoiced in arrears and the last invoice looks to be from last quarter -> ready to invoice
if not is_arrears and invoices[0][
'begin'] == pq_start and invoices[0]['end'] == pq_end:
self.add_new_invoice(invoices[0], cust, q_start, q_end, today,
3, price)
ready_to_invoice.append(cust)
continue
# If the customer is not invoiced in arrears and the last invoice is a partial invoice last quarter -> ready to invoice new customer
if not is_arrears and invoices[0]['end'] == pq_end:
cust['name'] += " (new customer)"
self.add_new_invoice(invoices[0], cust, q_start, q_end, today,
3, price)
ready_to_invoice.append(cust)
continue
# If the customer is invoiced in arrears and the last invoice looks to be from last last quarter -> ready to invoice
if is_arrears and invoices[0]['begin'] == ppq_start and invoices[
0]['end'] == ppq_end:
self.add_new_invoice(invoices[0], cust, pq_start, pq_end,
today, 3, price)
ready_to_invoice.append(cust)
continue
# If the customer is invoiced in arrears and the last invoice was from the prior quarter -> current
if is_arrears and invoices[0]['begin'] == pq_start and invoices[0][
'end'] == pq_end:
current.append(cust)
continue
# Check to see if this is an annual invoice
try:
end_dt = invoices[0]['end_dt']
begin_dt = invoices[0]['begin_dt']
delta = end_dt - begin_dt
if delta.days > 359 and delta.days < 366:
cust['name'] += " (annual)"
if time.mktime(end_dt.timetuple()) <= time.time():
end_date = datetime.date(
end_dt.year + 1, end_dt.month,
end_dt.day).strftime("%m-%d-%Y")
begin_date = datetime.date(
begin_dt.year + 1, begin_dt.month,
begin_dt.day).strftime("%m-%d-%Y")
self.add_new_invoice(invoices[0], cust, begin_date,
end_date, today, 12, price)
ready_to_invoice.append(cust)
else:
current.append(cust)
continue
except TypeError:
wtf.append(cust)
continue
# If the end date is after the curent date, then consider it current
if time.mktime(end_dt.timetuple()) > time.time():
current.append(cust)
continue
# Everyone else, WTF?
wtf.append(cust)
return render_template("quickbooks/index.html",
ready=ready_to_invoice,
wtf=wtf,
current=current)
