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 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_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_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_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_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_quickbooks_customer(self, qb_client):
if settings.QB_ACTIVE and qb_client:
if not self.qbo_id:
customer = Customer()
else:
customer = Customer.get(self.qbo_id, qb=qb_client)
customer.CompanyName = self.display_name
customer.DisplayName = self.display_name
customer.PrintOnCheckName = self.display_name
billing_address = self.get_billing_address()
if billing_address:
customer.BillAddr = QuickBooksAddress()
customer.BillAddr.Line1 = billing_address.street_address
customer.BillAddr.Line2 = billing_address.supplemental_address_1
customer.BillAddr.Line3 = billing_address.supplemental_address_2
customer.BillAddr.City = billing_address.city
customer.BillAddr.Country = billing_address.country.name
customer.BillAddr.CountrySubDivisionCode = (
billing_address.state_province_abbr)
customer.BillAddr.PostalCode = "{} {}".format(
billing_address.postal_code,
billing_address.postal_code_suffix).strip()
primary_contact = self.contact_set.filter(contact_type=6)[0]
accounting_contacts = self.contact_set.filter(contact_type=13)
if accounting_contacts:
accounting_emails = [
accounting_contact.email
for accounting_contact in accounting_contacts
]
else:
accounting_emails = [primary_contact.email]
customer.PrimaryEmailAddr = EmailAddress()
customer.PrimaryEmailAddr.Address = ", ".join(accounting_emails)
customer.GivenName = primary_contact.first_name
customer.FamilyName = primary_contact.last_name
customer.save(qb=qb_client)
self.qbo_id = customer.Id
self.save()
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)
#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}")
print(f"{len(last_years_customer_sales.keys())}, {len(this_years_customer_sales.keys())}, {len(this_years_customer_invoices.keys())}")
diff_sales = set(last_years_customer_sales.keys()) - set(this_years_customer_sales.keys())
diff_sales = diff_sales - set(this_years_customer_invoices.keys())
# Now that we have the diff lets find the customer name and scout
with open('customers_sold_in_2019_but_not_sold_in_2020.csv', 'w', newline='') as csvfile:
mulch_writer = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
mulch_writer.writerow(['scout', 'customerName', 'customerPhone', 'customerEmail'])
for diff in diff_sales:
customer = Customer.get(diff, qb=client)
#print(f"DiffCustomerName: {customer.DisplayName}, diffItem:{diff}, scout:{last_years_customer_sales[diff]}, contactPhone:{customer.PrimaryPhone}, customerEmail:{customer.PrimaryEmailAddr}")
mulch_writer.writerow([last_years_customer_sales[diff], customer.DisplayName, customer.PrimaryPhone, customer.PrimaryEmailAddr])
def check_and_update_customer_information(cr, customer_id):
# If the customer is found, then the tool will compare the address in the database by `<house number> <first token>` in the address. If this is a discrepancy, it prompts the user to make the change or not.
# If the phone number is a mismatch and there is a new one, it just replaces it and moves the old one to the 2nd phone field in the customer record.
# If the email is a mismatch and there is a new one, then it just replaces it wihtout prompting and moves it to the 2nd email field in the customer record.
customer = Customer.get(customer_id, qb=qb_client)
#phone number update
phone_new = cr.customer_phone
primary_phone_obj = customer.PrimaryPhone
#print(type(customer.PrimaryPhone))
if phone_new is not None and primary_phone_obj is not None:
phone_orig = customer.PrimaryPhone.FreeFormNumber
formatted_new = phonenumbers.format_number(
phonenumbers.parse(phone_new, "US"),
phonenumbers.PhoneNumberFormat.NATIONAL)
formatted_orig = phonenumbers.format_number(
phonenumbers.parse(phone_orig, "US"),
phonenumbers.PhoneNumberFormat.NATIONAL)
if formatted_new != formatted_orig:
#update the phone field in the customer
logging.warning(
"The database customer phone number:[{}] is different from the order: [{}]. Updating..."
.format(formatted_orig, formatted_new))
orig_phone_struct = PhoneNumber()
orig_phone_struct.FreeFormNumber = formatted_orig
customer.AlternatePhone = orig_phone_struct
customer.PrimaryPhone.FreeFormNumber = formatted_new
customer.save(qb_client)
else:
if phone_new is not None:
formatted_new = phonenumbers.format_number(
phonenumbers.parse(phone_new, "US"),
phonenumbers.PhoneNumberFormat.NATIONAL)
logging.warning(
"The database customer phone number is empty from the order: [{}]. Updating..."
.format(formatted_new))
new_phone_struct = PhoneNumber()
new_phone_struct.FreeFormNumber = formatted_new
customer.PrimaryPhone = new_phone_struct
customer.save(qb_client)
#Customer email update
customer = Customer.get(customer_id, qb=qb_client)
email_new = cr.customer_email
email_orig_obj = customer.PrimaryEmailAddr
if email_new is not None and email_orig_obj is not None:
email_orig = customer.PrimaryEmailAddr.Address
if email_orig != email_new:
#update the phone field in the customer
logging.warning(
"The database customer email:[{}] is different from the order: [{}]. Updating..."
.format(email_orig, email_new))
customer.PrimaryEmailAddr.Address = email_new
customer.save(qb_client)
else:
if email_new is not None:
logging.warning(
"The database customer email address is empty from the order: [{}]. Updating..."
.format(email_new))
new_email_struct = EmailAddress()
new_email_struct.Address = email_new
customer.PrimaryEmailAddr = new_email_struct
customer.save(qb_client)
#Customer address update
customer = Customer.get(customer_id, qb=qb_client)
address_line1_new = cr.customer_street
address_line1_old_obj = customer.BillAddr
if address_line1_new is not None and address_line1_old_obj is not None:
address_line1_old = customer.BillAddr.Line1
if address_line1_new != address_line1_old:
#update the phone field in the customer
logging.warning(
"The database billing address:[{}] is different from the order: [{}]. Updating..."
.format(address_line1_old, address_line1_new))
answer = yesno(
"Update the address from [{}] to [{}] for customer: [{}]".
format(address_line1_old, address_line1_new,
customer.DisplayName))
if answer:
customer.BillAddr.Line1 = address_line1_new
customer.BillAddr.City = cr.customer_city
customer.BillAddr.CountrySubDivisionCode = cr.customer_state
customer.BillAddr.PostalCode = cr.customer_zip
customer.ShipAddr = customer.BillAddr
try:
customer.save(qb_client)
except ValidationException as ve:
print(ve.detail)
else:
if address_line1_new is not None:
logging.warning(
"The database customer billing address is empty from the order: [{}]. Updating..."
.format(address_line1_new))
new_address_struct = Address()
new_address_struct.Line1 = address_line1_new
new_address_struct.City = cr.customer_city
new_address_struct.CountrySubDivisionCode = cr.customer_state
new_address_struct.PostalCode = cr.customer_zip
customer.BillAddr = customer.ShipAddr = new_address_struct
customer.save(qb_client)
redirect_uri='https://developer.intuit.com/v2/OAuth2Playground/RedirectUrl',
)
url = auth_client.get_authorization_url(scopes=[Scopes.ACCOUNTING])
print("finished authorizing...")
print('running api call...' + url)
client = QuickBooks(
auth_client=auth_client,
refresh_token=REFRESH_TOKEN,
company_id=COMPANY_ID,
)
print('finished connecting')
sales = SalesReceipt.query(f"SELECT * FROM salesreceipt where MetaData.CreateTime > '2019-12-01' MAXRESULTS 1000", qb=client)
cnt = 0
print(f"Scout, Customer, Phone, Email")
for sale in sales:
cnt += 1
scout = sale.CustomField[0].StringValue
if len(scout) == 0:
scout = "none"
else:
scout = scout.replace(',',': ')
customer = Customer.get(sale.CustomerRef.value, qb=client)
print(f"{scout},{customer.DisplayName},{customer.PrimaryPhone},{customer.PrimaryEmailAddr}")
def process_data():
rows = []
subdivision_data = load_subdivision_data()
sales_receipts = get_sales_receipts(PROCESSING_START_DATETIME, PROCESSING_END_DATETIME)
for receipt in sales_receipts:
for r in receipt.Line:
if r.Id is not None:
sr = MulchSalesReport()
#customer
c = Customer.get(receipt.CustomerRef.value, qb=qb_client)
sr.customer_name = c.DisplayName
sr.customer_first = c.GivenName
sr.customer_last = c.FamilyName
if c.ShipAddr is not None:
sr.customer_street = c.ShipAddr.Line1
sr.customer_city = c.ShipAddr.City
sr.customer_state = c.ShipAddr.CountrySubDivisionCode
sr.customer_zip = c.ShipAddr.PostalCode
raw_street_lookup = sr.customer_street.split(' ')[0:-1]
street_lookup = ' '.join(raw_street_lookup).strip().upper()
sr.subdivision = subdivision_data.get(street_lookup)
if c.BillAddr is not None:
sr.billing_street = c.BillAddr.Line1
sr.billing_city = c.BillAddr.City
sr.billing_state = c.BillAddr.CountrySubDivisionCode
sr.billing_zip = c.BillAddr.PostalCode
sr.customer_phone = c.PrimaryPhone
sr.customer_email = c.PrimaryEmailAddr
#sales receipt
sr.date = receipt.TxnDate
sr.sr_record_id = receipt.DocNumber
sr.date_modified = receipt.MetaData['LastUpdatedTime']
sr.sr_total_price = float(r.Amount)
scout_credit = receipt.CustomField[0]
if scout_credit.StringValue != '':
credit = scout_credit.StringValue.split(':')
if len(credit) > 1:
sr.unit_sale = credit[0].upper()
sr.scout_sale = credit[1].strip()
elif len(credit) == 1:
if re.findall(TROOP_KEYS, credit[0].lower()):
sr.unit_sale = credit[0].upper()
else:
sr.scout_sale = credit[0].strip()
else:
sr.scout_sale = scout_credit.StringValue.strip()
if receipt.CustomerMemo is not None:
sr.sr_product_memo = receipt.CustomerMemo['value']
deposit_account = receipt.DepositToAccountRef
if deposit_account is not None:
sr.unit_income = lookup_payer_name(deposit_account.name)
sr.sr_check_no = receipt.PaymentRefNum
sr.payment_method_name = receipt.PaymentMethodRef
#sales receipt line item
qty = r.SalesItemLineDetail['Qty']
#sr.sr_product_qty = qty
item = Item.get(r.SalesItemLineDetail['ItemRef']['value'], qb=qb_client)
sr.sr_product_sku = item.Sku
sr.sr_product_name = item.Name
sr.sr_product_price = r.SalesItemLineDetail['UnitPrice']
#color
item_name = sr.sr_product_name.lower()
if re.findall(BROWN_KEYS, item_name):
sr.brown_qty = qty
sr.sr_product_color = 'Brown'
elif re.findall(RED_KEYS,item_name):
sr.red_qty = qty
sr.sr_product_color = 'Red'
elif re.findall(BLACK_KEYS,item_name):
sr.black_qty = qty
sr.sr_product_color = 'Black'
elif re.findall(SPREAD_KEYS,item_name):
sr.spread_qty = qty
sr.spread_check_no = sr.sr_check_no
sr.spread_sale_no = receipt.DocNumber
sr.spread_total = sr.sr_total_price
if receipt.CustomerMemo is not None:
sr.spread_notes = receipt.CustomerMemo['value']
if re.findall('tbd', item.Name.lower()):
sr.spread_notes = "{} : SPREAD DATE TBD".format(sr.spread_notes)
else:
sr.spread_date = lookup_spreading_date(item.Name)
elif re.findall(DONATION_KEYS,item_name):
sr.donate_total = sr.sr_total_price
sr.sr_product_memo = ''
sr.sr_bags_qty = sr.black_qty + sr.brown_qty + sr.red_qty
#Reports
#result = qb_client.get_report('TransactionListByTagType')
#print(result)
if len(rows)%20 == 0:
print()
print('.', end='')
rows.append(sr)
return rows
