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}")