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 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 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_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 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 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)
def qbo_find_sales_account():
client = create_qbc()
account = Customer.filter(Active=True, Name="Sales", qb=client)
return account[0].Id
