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