def create_invoices(client, invoices):
'''
Given a set of existing invoices, fetch the invoice from QuickBooks, make a copy, update it
with new values and then have QuickBooks save the new invoice. Invoices are not sent,
and must be sent via the QuickBooks web interface.
'''
for invoice in invoices:
invoice_list = Invoice.query(
"select * from invoice where Id = '%s'" %
invoice['base_invoice'],
qb=client)
if not invoice_list:
flash("Cannot fetch old invoice!")
break
new_invoice = invoice_list[0]
new_invoice.Id = None
new_invoice.DocNumber = None
new_invoice.DueDate = None
new_invoice.TxnDate = None
new_invoice.ShipDate = None
new_invoice.EInvoiceStatus = None
new_invoice.MetaData = None
new_invoice.TotalAmt = None
new_invoice.EmailStatus = "NeedToSend"
new_invoice.Line[0].SalesItemLineDetail.Qty = invoice['qty']
new_invoice.Line[0].SalesItemLineDetail.UnitPrice = invoice[
'price']
new_invoice.Line[0].Amount = "%d" % round(
float(invoice['price']) * float(invoice['qty']))
new_invoice.CustomField[1].StringValue = invoice['begin']
new_invoice.CustomField[2].StringValue = invoice['end']
new_invoice.save(qb=client)
def invoice_search(invoice_number, customer, client=None):
"""
invoice_number
if == 1 then will force create a new one
999999999 will search for any existing
specific # will search for that invoice #
"""
if client is None:
client = get_quickbooks_client()
invoice = None
if invoice_number == "999999999":
# search for any NeedToSend invoices for chapter
# however, can not filter on EmailStatus directly
invoices = Invoice.query(
select=
f"select * from Invoice where balance > '0' AND CustomerRef = '{customer.Id}'",
qb=client,
)
for invoice_test in invoices:
if invoice_test.EmailStatus == "NeedToSend":
invoice = invoice_test
break
elif invoice_number != "1" and invoice_number != "":
# search for specific invoice number
invoices = Invoice.query(
select=
f"select * from Invoice where DocNumber = '{invoice_number}' AND CustomerRef = '{customer.Id}'",
qb=client,
)
if invoices:
invoice = invoices[0]
if invoice is None:
invoice = Invoice()
term = Term.filter(name="Two Weeks", qb=client)[0]
linenumber_count = 1
invoice.CustomerRef = customer.to_ref()
invoice.AllowOnlineACHPayment = True
invoice.BillEmail = customer.PrimaryEmailAddr
invoice.EmailStatus = "NeedToSend"
invoice.SalesTermRef = term.to_ref()
invoice.CustomerMemo = CustomerMemo()
else:
linenumber_count = len(invoice.Line)
return invoice, linenumber_count
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 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)
last_years_customer_sales.update({sale.CustomerRef.value: sale.ClassRef})
# Now get this year sales
this_year_sales = SalesReceipt.query(f"SELECT * FROM salesreceipt where TxnDate > '2019-12-01' and TxnDate < '2020-06-01' MAXRESULTS 1000", qb=client)
#cnt = 0
this_years_customer_sales = {}
for sale in this_year_sales:
#cnt += 1
#print(f"CustomerName: {sale.CustomerRef.name}, CustomerID:{sale.CustomerRef.value}, Scout: {sale.CustomField[0].StringValue}")
this_years_customer_sales.update({sale.CustomerRef.value: sale.CustomField[0].StringValue})
this_years_customer_invoices = {}
#this years invoices (from square)
this_year_invoices = Invoice.query(f"SELECT * FROM Invoice where TxnDate > '2019-12-01' and TxnDate < '2020-06-01' MAXRESULTS 1000", qb=client)
for invoice in this_year_invoices:
print(f"InvoiceCustomerName: {invoice.CustomerRef.name}, InvoiceCustomerID:{invoice.CustomerRef.value}, InvoiceScout: {invoice.CustomField[0].StringValue}")
this_years_customer_invoices.update({invoice.CustomerRef.value: invoice.CustomField[0].StringValue})
#now find the difference
#list(set(temp1) - set(temp2))
# #print out last year, then this year
# for last_key, last_value in last_years_customer_sales.items():
# print(f"last_key:{last_key}, last value:{last_value}")
#
# #print out last year, then this year
# for this_key, this_value in this_years_customer_sales.items():
# print(f"this_key:{this_key}, this value:{this_value}")
