public static List <Quickbooks.DataTypes.Preferences.CustomField> EnumerateCustomFields() { try { String query = @" SELECT * FROM Preferences "; query = PeakeyTools.Database.MySQL.TrimQueryText(query); dynamic response = JsonConvert.DeserializeObject(Quickbooks.SubmitQuery(query)); if (response != null && response.QueryResponse != null && response.QueryResponse.Preferences != null) // Custom fields exist { List <Quickbooks.DataTypes.Preferences.CustomField> customFields = new List <DataTypes.Preferences.CustomField>(); foreach (var item in response.QueryResponse.Preferences[0].SalesFormsPrefs.CustomField[1].CustomField) { Quickbooks.DataTypes.Preferences.CustomField customField = new Quickbooks.DataTypes.Preferences.CustomField { ID = PeakeyTools.Database.MySQL.NullInt32(item.Name.Value.Substring(item.Name.Value.Length - 1, 1)), // Parse ID since QB includes the ID in the name field for some reason (Can only have up to 3 custom fields, so subtract 1) Name = item.Name.Value, Type = item.Type.Value, StringValue = item.StringValue.Value }; customFields.Add(customField); } if (customFields.Count > 0) { return(customFields); } } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while attempting to enumerate Quickbook custom fields. " + ex.Message, "EXCEPTION"); throw new Exception("Could not enumerate Quickbook custom fields.", ex); } }
public static List <Quickbooks.DataTypes.Term> Enumerate() { try { String query = @" SELECT * FROM Term "; query = PeakeyTools.Database.MySQL.TrimQueryText(query); dynamic response = JsonConvert.DeserializeObject(Quickbooks.SubmitQuery(query)); if (response != null && response.QueryResponse != null && response.QueryResponse.Term != null) // Term exists { List <Quickbooks.DataTypes.Term> terms = new List <DataTypes.Term>(); foreach (var item in response.QueryResponse.Term) { Quickbooks.DataTypes.Term term = new Quickbooks.DataTypes.Term { ID = PeakeyTools.Database.MySQL.NullInt32(item.Id.Value), Name = item.Name.Value, Active = PeakeyTools.Database.MySQL.NullBoolean(item.Active.Value) }; terms.Add(term); } if (terms.Count > 0) { return(terms); } } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while attempting to enumerate Quickbook terms. " + ex.Message, "EXCEPTION"); throw new Exception("Could not enumerate Quickbook terms.", ex); } }
public static Quickbooks.DataTypes.Item FindByName(String itemName) { try { String query = String.Format(@" SELECT * FROM Item WHERE Name = '{0}' ", itemName); query = PeakeyTools.Database.MySQL.TrimQueryText(query); dynamic response = JsonConvert.DeserializeObject(Quickbooks.SubmitQuery(query)); if (response != null && response.QueryResponse != null && response.QueryResponse.Item != null) { dynamic data = response.QueryResponse.Item[0]; Quickbooks.DataTypes.Item item = new Quickbooks.DataTypes.Item { ID = PeakeyTools.Database.MySQL.NullInt32(data.Id.Value), Name = data.Name.Value }; return(item); } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Could not find Quickbook Item by name. [Item Name]: " + itemName + " " + ex.Message, "EXCEPTION"); throw new Exception("An error has occurred while checking if the customer exists in Quickbooks.", ex); } }
public static Quickbooks.DataTypes.Customer Create(String firstName, String lastName) { try { if (firstName == "") { return(null); } if (lastName == "") { return(null); } // Check if customer already exists in Quickbooks Quickbooks.DataTypes.Customer existingCustomer = FindExisting(firstName, lastName); if (existingCustomer != null) { return(existingCustomer); } // Create customer dynamic customerInfo = null; customerInfo = new System.Dynamic.ExpandoObject(); customerInfo.GivenName = firstName; customerInfo.FamilyName = lastName; customerInfo.DisplayName = firstName + " " + lastName; dynamic response = JsonConvert.DeserializeObject(Quickbooks.PostRequest("customer", JsonConvert.SerializeObject(customerInfo))); if (response != null && response.Customer != null) { dynamic data = response.Customer; Quickbooks.DataTypes.Customer customer = new Quickbooks.DataTypes.Customer { ID = PeakeyTools.Database.MySQL.NullInt32(data.Id.Value), GivenName = data.GivenName.Value, FamilyName = data.FamilyName.Value }; if (data.SalesTermRef != null) { if (data.SalesTermRef.value != null) { customer.TermID = PeakeyTools.Database.MySQL.NullInt32(data.SalesTermRef.value.Value); } if (data.SalesTermRef.name != null) { customer.TermName = data.SalesTermRef.name.Value; } } if (data.PrimaryEmailAddr != null) { customer.PrimaryEmailAddr = data.PrimaryEmailAddr.Address.Value; } if (data.PrimaryPhone != null) { customer.PrimaryPhone = data.PrimaryPhone.FreeFormNumber.Value; } return(customer); } else if (response != null && response.Fault != null) { dynamic data = response.Fault.Error[0]; Quickbooks.DataTypes.Error error = new DataTypes.Error { Message = data.Message.Value, Detail = data.Detail.Value, Code = data.code.Value }; PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while creating the customer in Quickbooks. " + "\t[Error Message]: " + error.Message + "\t[Error Detail]: " + error.Detail + "\t[First Name]: " + firstName + "\t[Last Name]: " + lastName, "EXCEPTION"); return(null); } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while creating the customer in Quickbooks. " + ex.Message + "\t[First Name]: " + firstName + "\t[Last Name]: " + lastName, "EXCEPTION"); throw new Exception("An error has occurred while creating the customer in Quickbooks.", ex); } }
private static Quickbooks.DataTypes.Customer FindExisting(String firstName, String lastName) { try { String query = String.Format(@" SELECT * FROM Customer WHERE GivenName = '{0}' AND FamilyName = '{1}' ", firstName, lastName); query = PeakeyTools.Database.MySQL.TrimQueryText(query); dynamic response = JsonConvert.DeserializeObject(Quickbooks.SubmitQuery(query)); if (response != null && response.QueryResponse != null && response.QueryResponse.Customer != null) // Customer exists { dynamic data = response.QueryResponse.Customer[0]; Quickbooks.DataTypes.Customer customer = new Quickbooks.DataTypes.Customer { ID = PeakeyTools.Database.MySQL.NullInt32(data.Id.Value), GivenName = data.GivenName.Value, FamilyName = data.FamilyName.Value }; if (data.SalesTermRef != null) { customer.TermID = PeakeyTools.Database.MySQL.NullInt32(data.SalesTermRef.value); customer.TermName = data.SalesTermRef.name; } if (data.DisplayName != null) { customer.DisplayName = data.DisplayName.Value; } if (data.PrimaryEmailAddr != null) { customer.PrimaryEmailAddr = data.PrimaryEmailAddr.Address.Value; } if (data.PrimaryPhone != null) { customer.PrimaryPhone = data.PrimaryPhone.FreeFormNumber.Value; } return(customer); } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while checking if the customer exists in Quickbooks. " + ex.Message + "\t[First Name]: " + firstName + "\t[Last Name]: " + lastName, "EXCEPTION"); throw new Exception("An error has occurred while checking if the customer exists in Quickbooks.", ex); } }
private static List <Quickbooks.DataTypes.Invoice> EnumerateByCustomer(DataTypes.Customer customer) { try { String query = String.Format(@" SELECT Id, CustomField, DocNumber, SyncToken FROM Invoice WHERE CustomerRef = '{0}' ", customer.ID); query = PeakeyTools.Database.MySQL.TrimQueryText(query); dynamic response = JsonConvert.DeserializeObject(Quickbooks.SubmitQuery(query)); if (response != null && response.QueryResponse != null && response.QueryResponse.Invoice != null) // Invoice exists { List <Quickbooks.DataTypes.Invoice> invoices = new List <Quickbooks.DataTypes.Invoice>(); foreach (var item in response.QueryResponse.Invoice) { Quickbooks.DataTypes.Invoice invoice = new Quickbooks.DataTypes.Invoice { ID = PeakeyTools.Database.MySQL.NullInt32(item.Id.Value), SyncToken = PeakeyTools.Database.MySQL.NullInt32(item.SyncToken.Value) // Required on update, to lock an object for use (will receive an 'Stale Object Error' if not used) }; if (item.DocNumber != null) { invoice.DocNumber = PeakeyTools.Database.MySQL.NullString(item.DocNumber.Value); } // Loop through each custom field foreach (var customFieldItem in item.CustomField) { if (customFieldItem.StringValue != null) { switch (customFieldItem.Name.Value.ToLower()) { case "sales rep": invoice.SalesRep = customFieldItem.StringValue.Value; break; case "office id": invoice.OfficeID = customFieldItem.StringValue.Value; break; } } } invoices.Add(invoice); } if (invoices.Count > 0) { return(invoices); } } return(null); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Could not enumerate Quickbook invoices by customer. " + ex.Message + "\t[First Name]: " + customer.GivenName + "\t[Last Name]: " + customer.FamilyName, "EXCEPTION"); throw new Exception("Could not enumerate Quickbook invoices by customer.", ex); } }
private static dynamic Create(DataTypes.Customer customer, DataTypes.Transaction transaction, DataTypes.Item itemProductAndServices, DataTypes.Term termDueOnReceipt, List <DataTypes.Preferences.CustomField> customFields, List <DataTypes.AccountingTypes> accountingTypes) { try { if (customer == null) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Quickbook 'customer' object is null. Cannot create invoice.", "EXCEPTION"); return(PeakeyTools.Api.ToJSON(false, true, "Quickbook 'customer' object is null. Cannot create invoice.")); } if (transaction == null) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Quickbook 'transaction' object is null. Cannot create invoice.", "EXCEPTION"); return(PeakeyTools.Api.ToJSON(false, true, "Quickbook 'transaction' object is null. Cannot create invoice.")); } if (customFields == null) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Quickbook 'customFields' object is null. Cannot create invoice.", "EXCEPTION"); return(PeakeyTools.Api.ToJSON(false, true, "Quickbook 'customFields' object is null. Cannot create invoice.")); } // Create invoice dynamic invoiceInfo = new System.Dynamic.ExpandoObject(); invoiceInfo.TxnDate = transaction.Date; invoiceInfo.DocNumber = transaction.OrderID; // 'Custom transaction numbers' setting must be false under 'Company Settings' // Check if an invoice already exists with this transactionID and orderID ('transactionID-orderID' value is set in the custom field 'Office ID') Quickbooks.DataTypes.Invoice existingInvoice = FindExisting(customer, transaction); if (existingInvoice != null) // An invoice already exists { invoiceInfo.Id = existingInvoice.ID; // Setting an invoice ID in the json will make the API update the record invoiceInfo.SyncToken = existingInvoice.SyncToken; } // For the invoice date to change, MetaData.CreateTime & TxnDate must be set with the same date/time and format invoiceInfo.MetaData = new System.Dynamic.ExpandoObject(); invoiceInfo.MetaData.CreateTime = transaction.Date; invoiceInfo.CustomerRef = new System.Dynamic.ExpandoObject(); invoiceInfo.CustomerRef.value = customer.ID; invoiceInfo.CustomerRef.name = customer.DisplayName; // Invoice line items invoiceInfo.Line = new List <dynamic>(); foreach (var item in accountingTypes) { if (item.Total == 0) // Ignore accounting types with an amount of 0.00 { continue; } dynamic products = new System.Dynamic.ExpandoObject(); products.DetailType = "SalesItemLineDetail"; products.Description = item.Title; products.Amount = item.Total; products.SalesItemLineDetail = new System.Dynamic.ExpandoObject(); products.SalesItemLineDetail.Qty = 0; //products.SalesItemLineDetail.UnitPrice = item.UnitPrice; if (itemProductAndServices != null) { products.SalesItemLineDetail.ItemRef = new System.Dynamic.ExpandoObject(); products.SalesItemLineDetail.ItemRef.value = itemProductAndServices.ID; products.SalesItemLineDetail.ItemRef.name = itemProductAndServices.Name; } invoiceInfo.Line.Add(products); } // Invoice term //if (customer.TermID != 0) //{ // invoiceInfo.SalesTermRef = new System.Dynamic.ExpandoObject(); // invoiceInfo.SalesTermRef.value = customer.TermID; // if (customer.TermName != null) // { // invoiceInfo.SalesTermRef.name = customer.TermName; // } //} if (termDueOnReceipt != null) { invoiceInfo.SalesTermRef = new System.Dynamic.ExpandoObject(); invoiceInfo.SalesTermRef.value = termDueOnReceipt.ID; invoiceInfo.SalesTermRef.name = termDueOnReceipt.Name; } // Invoice custom fields invoiceInfo.CustomField = new List <dynamic>(); foreach (var item in customFields) { dynamic customField = new System.Dynamic.ExpandoObject(); switch (item.StringValue.ToLower()) { case "office id": customField.DefinitionId = item.ID; customField.Name = item.Name; customField.Type = item.Type; customField.StringValue = transaction.OfficeID; invoiceInfo.CustomField.Add(customField); break; case "sales rep": customField.DefinitionId = item.ID; customField.Name = item.Name; customField.Type = item.Type; customField.StringValue = transaction.SalesEmployeeFirstName; invoiceInfo.CustomField.Add(customField); break; } } // Submit request dynamic response = JsonConvert.DeserializeObject(Quickbooks.PostRequest("invoice?include=allowduplicatedocnum", JsonConvert.SerializeObject(invoiceInfo))); // Allow duplicate invoice numbers when the 'Custom transaction numbers' setting is checked if (response != null && response.Invoice != null) { return(PeakeyTools.Api.ToJSON(true, false, "Exported transaction to Quickbooks successfully.")); } else if (response != null && response.Fault != null) { dynamic data = response.Fault.Error[0]; Quickbooks.DataTypes.Error error = new DataTypes.Error { Message = data.Message.Value, Detail = data.Detail.Value, Code = data.code.Value }; PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while creating the invoice in Quickbooks. " + "\t[Error Message]: " + error.Message + "\t[Error Detail]: " + error.Detail + "\t[OrderID]: " + transaction.OrderID + "\t[Customer Name]: " + customer.DisplayName + "\t[Transaction Date]: " + transaction.Date, "EXCEPTION"); return(PeakeyTools.Api.ToJSON(false, false, "Failed to add a Quickbook invoice. [Error Message]: " + error.Message + " [Error Detail]: " + error.Detail)); } else { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "Failed to add a Quickbook invoice." + "\t[OrderID]: " + transaction.OrderID + "\t[Customer Name]: " + customer.DisplayName + "\t[Transaction Date]: " + transaction.Date, "EXCEPTION"); } return(PeakeyTools.Api.ToJSON(false, false, "Failed to add a Quickbook invoice.")); } catch (Exception ex) { PeakeyTools.Api.Log(PeakeyTools.Api.ApiName.quickbooks, "An error has occurred while creating the invoice in Quickbooks. " + ex.Message + "\t[OrderID]: " + transaction.OrderID + "\t[Customer Name]: " + customer.DisplayName + "\t[Transaction Date]: " + transaction.Date, "EXCEPTION"); throw new Exception("An error has occurred while creating the invoice in Quickbooks.", ex); } }