QuickBooks Sync

QuickBooks Sync regroups multiple NuGet packages to sync data from QuickBooks Desktop using QbXml. Make requests to QuickBooks Desktop, analyze the returned values, etc.

This project is actively maintained and is in its early alpha stage. Many breaks will be introduced until stability is reached.

QbXml

QbXml is the language used by QuickBooks desktop to exchange back and forth data between an application and the QuickBooks database.

Here is a couple of ideas how you can make some requests and parse responses.

Create a request XML with QbXml

public class CustomerRequest
{
  public CustomerRequest() 
  {
    var request = new QbXmlRequest();
    var innerRequest = new CustomerQueryRqType();

    // Add some filters here
    innerRequest.MaxReturned = "100";
    innerRequest.FromModifiedDate = new DATETIMETYPE(DateTime.Now);

    request.AddToSingle(innerRequest);

    // Get the XML
    var xml = request.GetRequest();
  }
}

Receive a response from QuickBooks and parse the QbXml

public class CustomerResponse
{
  public void LoadResponse(string xml)
  {
    var response = new QbXmlResponse();
    var rs = response.GetSingleItemFromResponse<CustomerQueryRsType>(xml);

    // Receive customer objects, corresponding to the xml
    var customers = rs.CustomerRet;
  }
}

Web Connector

Thanks to the Web Connector, you can communicate with QuickBooks Desktop. Users must download it at the following address: http://marketplace.intuit.com/webconnector/

How does it work?

Once downloaded, they must get a QWC file that will connect the Web Connector to your website. To generate a QWC file, use the following method

/// TODO

The Web Connector uses the SOAP protocol to talk with your website, the NuGet package takes care of the heavy lifting to talk with the QuickBooks Desktop. However, you must implement some methods in order to get everything working according to your needs. The Web Connector will come periodically to your website asking if you have any requests to do to its database. The Web Connector executes the following tasks:

Authenticate - Sends the login/password that you must verify. You also return a session ticket that will be used for the rest of messages that are exchanged back and forth.
SendRequestXML - The Web Connector expects that you return an XML command that will execute on the database.
ReceiveRequestXML - Response regarding the previous step.
GOTO Step 2 - Until you return an empty string, indicating that you are done.
CloseConnection - Connection is done.

Database state

Multiple "synchronous" connections are done between the Web Connector and your website. There is a need to keep some states with a database. The NuGet package doesn't provide such thing because it doesn't know which database provider/schema you are using; this is where you have to start coding your own implementation.

Step 1. Create an Authenticator

public interface IAuthenticator
{
  AuthenticatedTicket GetAuthenticationFromLogin(string login, string password);
  AuthenticatedTicket GetAuthenticationFromTicket(string ticket);
}

GetAuthenticationFromLogin - Authenticate a user from its login/password combination
GetAuthenticationFromTicket - Authenticate a ticket previously given from a GetAuthenticationFromLogin call

The AuthenticatedTicket contains 3 properties:

public class AuthenticatedTicket
{
  public virtual string Ticket { get; set; }
  public virtual string CurrentStep { get; set; }
  public virtual bool Authenticated { get; set; }
}

Ticket - Exchanged with the Web Connector. It acts as a session identifier.
CurrentStep - State indicating at which step the Web Connector is currently at.
Authenticated - Simple boolean indicating if the ticket is authenticated.

If a user is not authenticated, make sure to return a ticket value, but set the Authenticated to false.

Step 2. Create a SyncManager

Extend the QbSync.WebConnector.SyncManager and override only the methods that you really need. You will most likely need a database context, make sure you get it from your constructor. From your constructor, register the steps you want to execute:

public SyncManager(ApplicationDbContext db_context, IOwinContext owinContext, IAuthenticator authenticator)
  : base(authenticator)
{
  this.db_context = db_context;
  this.owinContext = owinContext;

  RegisterStep(new CustomerQuery(this.db_context));
}

Important methods to override:

SaveChanges - Called before returning any data to the Web Connector. It's time to save data to your database.
LogMessage - Data going in or out goes through this method, you can save it to a database in order to better debug.
GetWaitTime - Tells the Web Connector to come back in X seconds.

Other methods you might want to override, but they are handled by the package:

Authenticate - Verifies if the login/password is correct. Returns appropriate message to the Web Connector in order to continue further communication.
ServerVersion - Returns the server version.
ClientVersion - Indicates which version is the Web Connector. Returns W: to return a warning; E: to return an error. Empty string if everything is fine.
SendRequestXML - The Web Connector is asking what has to be done to its database. Return an QbXml command.
ReceiveRequestXML - Response from the Web Connector based on the previous command sent.
GetLastError - Gets the last error that happened. This method is called only if an error is found.
ConnectionError - An error happened with the Web Connector.
CloseConnection - Closing the connection. Return a string to show to the user in the Web Connector.
OnException - Called if any of your steps throw an exception. It would be a great time to log this exception for future debugging.
ProcessClientInformation - Called when the WebConnector first connect to the service. It contains the information about the QuickBooks database.
GetOptions - Returns QbXml options. Used for TimeZone bug. See below.

Step 3. Register your Step Manager with the ASMX

The registration allows you to create a StepManager with any dependencies that you would like. Here is an example:

QbSync.WebConnector.QBConnectorSync.SyncManager = (QbSync.WebConnector.QBConnectorSync qbConnectorSync) =>
{
  return new MyOwn.SyncManager(kernel.Get<ApplicationDbContext>(), qbConnectorSync.Context.GetOwinContext());
};

Step 4. Creating a step

By registering a step such as CustomerQuery, you can get customers from the QuickBooks database. Since all steps will require a database manipulation on your side, you have to implement it yourself. But don't worry, it is pretty simple.

Here is an example:

public class CustomerQuery : StepQueryResponseBase<CustomerQueryRqType, CustomerQueryRsType>
{
  private ApplicationDbContext db_context;

  public CustomerQuery(ApplicationDbContext db_context)
    : base()
  {
    this.db_context = db_context;
  }

  public string GetName()
  {
    // Step name
    return "CustomerQuery";
  }

  protected override bool ExecuteRequest(AuthenticatedTicketContext authenticatedTicket, CustomerQueryRqType request)
  {
    // Do some operations on the customerRequest to get only specific ones
    customerRequest.FromModifiedDate = new DateTimeType(DateTime.Now);

    // Return false if you want to prevent the request to execute and go to the next step.
    return base.ExecuteRequest(authenticatedTicket, request);
  }

  protected override void ExecuteResponse(AuthenticatedTicketContext authenticatedTicket, CustomerQueryRsType response)
  {
    base.ExecuteResponse(authenticatedTicket, response);

    // Execute some operations with your database.
  }
}

The 2 generic classes are provided by the QbXml NuGet package. You associate the request and the response. They implement QbRequest and QbResponse.

Step 4.1. Creating a step with an iterator

When you make a request to the QuickBooks database, you might receive hundreds of objects back. Your server or the database won't be able to handle that many; you have to break the query into batches. We have everything handled for you, but we need to save another state to the database. Instead of deriving from StepQueryResponseBase, you have to derive from StepQueryWithIterator and implement 2 methods.

protected abstract void SaveMessage(string ticket, string step, string key, string value);
protected abstract string RetrieveMessage(string ticket, string step, string key);

Save the message to the database based on its ticket, step and key. Then retrieve it from the same keys.

By default, if you derive from the iterator, the query is batched with 100 objects.

The requests and responses that support an iterator implements QbIteratorRequest and QbIteratorResponse.

Step 5. Create a VersionValidator

QuickBooks supports multiple versions. However, this package supports only version 13.0 and above. In order to validate a request, you must provide a IVersionValidator. The reason this package cannot validate the version is because of the nature of the WebConnector: it takes 2 calls from the WebConnector to validate the version then warn the user.

The first call sends a version to your server. You can validate the version and must save the ticket for reference in the second call.
The second call, you need to tell the WebConnector the version was wrong based on the ticket saved in step 1.

Since this is done with two requests, the first request must persist that the version is wrong based on the ticket. With IsValidTicket, simply check if the ticket has been saved in your database (as invalid). If you find the ticket in your database, you can safely remove it from it as this method will not be called again with the same ticket.

Step X

You can register other steps such as UpdateCustomer, InvoiceQuery, etc. Just make your own!

QuickBooks TimeZone bug

QbXml doesn't handle Daylight Saving Time properly when it comes to DateTime. When the Daylight Saving Time is activated, the times returned by QuickBooks are off by the delta DST (typically 1h).

To overcome this problem, if you provide a TimeZoneInfo, QbXml package will fix the times that are not properly set. Use the QbXmlResponseOptions.TimeZoneBugFix by overriding SyncManager.GetOptions() and provide the timezone where QuickBooks Desktop is installed.

Contributing

Contributions are welcome. Code or documentation!

Fork this project
Create a feature/bug fix branch
Push your branch up to your fork
Submit a pull request

License

QuickBooksSync is under the MIT license.

mscappini/quickbooks-sync