def lookup_product(product_name): #Get the Item #items = Item.all(qb=qb_client) item_count = Item.count("Name = '" + product_name + "'", qb=qb_client) if item_count == 1: items = Item.where("Name = '" + product_name + "'", qb=qb_client) return items[0].Id else: logging.error("Product name: [{}] not found. Please add this product to quickbook before this record can be added".format(product_name))
def test_to_ref(self): item = Item() item.Name = "test" item.Id = 100 ref = item.to_ref() self.assertEquals(ref.name, "test") self.assertEquals(ref.type, "Item") self.assertEquals(ref.value, 100)
def qbo_check_item(soi): client = create_qbc() items = Item.filter(Active=True, Sku=soi.sku, qb=client) if(len(items) == 0): items = Item.filter(Active=True, Name=soi.title, qb=client) if(len(items) == 0): return qbo_create_item(soi) else: return items[0].Id else: return items[0].Id
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 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_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 create_line(item_count, linenumber_count, name=None, minimum=None, client=None): today = datetime.datetime.today().strftime("%Y/%m/%d") if client is None: client = get_quickbooks_client() line = SalesItemLine() line.LineNum = linenumber_count item = Item.filter(name=name, qb=client)[0] line.Description = item.Description total = item_count * item.UnitPrice unit = item.UnitPrice if minimum is not None: total = max(minimum, total) if total == minimum: # API requires that UnitPrice * Qty == Amount Error 6070 item_count = 1 unit = total line.Description = "AT MINIMUM;\n" + line.Description line.Amount = total line.SalesItemLineDetail = SalesItemLineDetail() line.SalesItemLineDetail.ItemRef = item.to_ref() line.SalesItemLineDetail.Qty = item_count line.SalesItemLineDetail.ServiceDate = today line.SalesItemLineDetail.UnitPrice = unit return line
def get_items_for_bill(bill, client): ids = [ l['ItemBasedExpenseLineDetail']['ItemRef']['value'] for l in bill['Line'] if 'ItemBasedExpenseLineDetail' in l ] items = Item.choose(ids, field="Id", qb=client) return [json.loads(i.to_json()) for i in items]
def index(): client = create_qbc() item = Item() item.Name = "test" item.UnitPrice = 100 item.Type = "Service" item.save(qb=client) return item.Id
def qbo_create_item(soi): client = create_qbc() item = Item() print(soi.title) print(soi.price) item.Name = soi.title item.UnitPrice = soi.price item.Type = "Service" item.Sku = soi.sku account = Account.filter(Active=True, Name="Sales", qb=client) account_ref = Account.get(account[0].Id, qb=client).to_ref() item.IncomeAccountRef = account_ref item.save(qb=client) return item.Id
def get_inventory_items(request, pos, all_stock=False): client = get_qbo_client(get_callback_url(request)) results = Item.query( 'SELECT * from Item WHERE Active = true STARTPOSITION %s MAXRESULTS %s' % (pos, settings.QBO_MAX_RESULTS), qb=client) # conditionally return all items regardless if they're in stock or not if all_stock: return results # limit items that are in stock return [r for r in results if r.QtyOnHand > 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 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 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 send_to_quickbooks(self, request): # Before we ship to quickbooks, let's save the freshsheet used by this Order self.freshsheet = FreshSheet.objects.latest('published_at') self.save() client = get_qb_client() customer = Ref() # customer.value = 1 customer.value = self.created_by.qb_customer_id # customer.name = self.created_by.req_info.business_name customer.type = 'Customer' line_items = [] for item in self.items.all(): item_lookup = Item.where( f"Name = '{item.item.name}{item.unit_quantity}'", qb=client) if item_lookup: product = item_lookup[0] product.UnitPrice = item.unit_cost product.Type = 'NonInventory' product.IncomeAccountRef = Account.where( "Name = 'Sales'", qb=client)[0].to_ref() product.save(qb=client) else: product = Item() product.Name = f"{item.item.name}{item.unit_quantity}" product.UnitPrice = item.unit_cost product.Type = 'NonInventory' product.IncomeAccountRef = Account.where( "Name = 'Sales'", qb=client)[0].to_ref() product.save(qb=client) line_detail = SalesItemLineDetail() line_detail.ItemRef = product.to_ref() line_detail.UnitPrice = item.unit_cost # in dollars line_detail.Qty = item.quantity # quantity can be decimal # Need to change this date to be the DELIVERY DATE of shipment, # not the date on which it was created # Check if it's between Sunday and Tuesday (Yields Tuesday date().isoformat()) # Check if it's between Wednesday and Friday (Yields Friday date().isoformat()) line_detail.ServiceDate = get_next_service_date().isoformat() line = SalesItemLine() line.Id = '1' line.Amount = item.total_cost # in dollars line.Description = f"{item.quantity} {item.item.get_unit_verbose()} of {product.Name} from " \ f"{item.item.farm}." line.SalesItemLineDetail = line_detail line_items.append(line) invoice = Invoice() invoice.CustomerRef = customer invoice.Line = line_items invoice.save(qb=client) # NOTE: If we try to just save the user model, it _could_ overwrite some Quickbooks auth settings. # By getting a fresh model we'll for sure have the latest settings fresh_user_model = User.objects.get(pk=request.user.pk) fresh_user_model.cart = None fresh_user_model.save()
def test_create(self): item = Item() item.Name = self.name item.Type = "Inventory" item.TrackQtyOnHand = True item.QtyOnHand = 10 item.InvStartDate = "2015-01-01" item.IncomeAccountRef = self.income_account.to_ref() item.ExpenseAccountRef = self.expense_account.to_ref() item.AssetAccountRef = self.asset_account.to_ref() item.save(qb=self.qb_client) query_item = Item.get(item.Id, qb=self.qb_client) self.assertEquals(query_item.Id, item.Id) self.assertEquals(query_item.Name, self.name) self.assertEquals(query_item.Type, "Inventory") self.assertEquals(query_item.TrackQtyOnHand, True) self.assertEquals(query_item.QtyOnHand, 10) self.assertEquals(query_item.IncomeAccountRef.value, self.income_account.Id) self.assertEquals(query_item.ExpenseAccountRef.value, self.expense_account.Id) self.assertEquals(query_item.AssetAccountRef.value, self.asset_account.Id)
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
def test_unicode(self): item = Item() item.Name = "test" self.assertEquals(str(item), "test")
def create_order(sr): #Get the sales receipt sales_receipt = SalesReceipt() sr_body = { "domain": "QBO", "Balance": 0, "CustomerRef": { "name": "", "value": "6" }, "CustomerMemo": { "value": "" }, "sparse": "false", "Line": [{ #"Description": "Custom Design", "DetailType": "SalesItemLineDetail", #required "SalesItemLineDetail": { "Qty": 1, #"UnitPrice": 75, "ItemRef": { #required "value": "44" #black mulch (1-9) } }, "LineNum": 1, "Amount": 0, }], "CustomField": [{ "DefinitionId": "1", "Name": "Scout Credit", "Type": "StringType", "StringValue": "" }], "PaymentMethodRef": { "value": sr.payment_method_ref }, "DepositToAccountRef": { "value": sr.deposit_account_ref }, "CustomerMemo": { "value": sr.memo }, } #amys = Customer.filter(start_position=1, max_results=25, Active=True, FamilyName="Smith", qb=qb_client) #amys = Customer.query("SELECT * from Customers where FamilyName='Smith'", qb=qb_client) #amys = qb_client.query("select count(*) from Customer Where Active=true and DisplayName LIKE '%Smith'") #customer_street_number = sr.customer_street.split(' ')[0] query = "Active=true and DisplayName = '" + sr.customer_name.lower() + "'" try: customers_count = Customer.count(query, qb=qb_client) except ValidationException as ve: print(ve.detail) if customers_count == 0: # create a new customer? if AUTO_CREATE_CUSTOMERS: answer = yesno( "Customer [{}] not found residing on [{}]. Create the customer?" .format(sr.customer_name, sr.customer_street)) if answer: logging.info( "Creating the customer [{}] in quickbooks.".format( sr.customer_name)) customer = create_customer(sr) if customer is not None: customers_count = 1 else: logging.warning( "Customer [{}] not found. Not creating customer due to settings." .format(sr.customer_name)) if customers_count == 1: #we have found a customer customers = Customer.where("Active=true and DisplayName LIKE '%" + sr.customer_name.lower() + "'", qb=qb_client) customer_id = customers[0].Id customer_name = customers[0].DisplayName logging.debug("Customer id: {}".format(customer_id)) if customer_id is not None: check_and_update_customer_information(sr, customer_id) sr_body['CustomerRef']['value'] = customer_id sr_body['CustomerRef']['name'] = customer_name sr_body['Line'][0]['Amount'] = sr.total_price product_id = lookup_product(sr.product_name) sr_body['Line'][0]['SalesItemLineDetail']['ItemRef'][ 'value'] = product_id sr_body['Line'][0]['SalesItemLineDetail']['Qty'] = sr.product_qty sr_body['Line'][0]['SalesItemLineDetail'][ 'UnitPrice'] = sr.product_price logging.debug("Revised Customer: {}".format(sr_body)) #print("SR Body: {}".format(sr_body)) #post a new one sales_receipt = sales_receipt.from_json(sr_body) sales_receipt.TxnDate = sr.date #check for duplicates #get all customer sales receipts duplicate = False srs = SalesReceipt.filter(CustomerRef=customer_id, qb=qb_client) for asr in srs: #get item ref info item = Item.get( asr.Line[0].SalesItemLineDetail['ItemRef']['value'], qb=qb_client) #print(asr.Line[0].SalesItemLineDetail['ItemRef']['name']) asr_date = str(parse(asr.TxnDate).date()) sr_date = str(parse(sr.date).date()) if item.Name == sr.product_name \ and asr_date == sr_date \ and asr.Line[0].SalesItemLineDetail['Qty'] == sr.product_qty \ and float(asr.TotalAmt) == float(sr.total_price): logging.warning( "found a duplicate for this customer: {} on {} for item: {}, qty: {}, total: {}. skipping..." .format(sr.customer_name, sr.date, sr.product_name, sr.product_qty, sr.total_price)) duplicate = True #add the item if not duplicate: try: sales_receipt.save(qb_client) logging.debug("SentBody: {}".format(json.dumps(sr_body))) logging.info( "Successful entry of SalesReceipt: [{}] into quickbooks. OrderId:[{}], Item:[{}], Qty:[{}], Total:[{}]" .format(sr.customer_last, sales_receipt.Id, sr.product_name, sr.product_qty, sr.total_price)) except QuickbooksException as e: logging.error("An error saving the sales_receipt: {}".format( e.detail)) elif customers_count > 1: logging.warning( "More than one customer matches name: [{}]. Cannot process record. Skipping." .format(sr.customer_last)) else: print("no customer found")
def test_valid_object_name(self): obj = Item() client = QuickBooks() result = client.isvalid_object_name(obj.qbo_object_name) self.assertTrue(result)