Query for All Invoices With Open Balances using Qu

Question

I am trying to Query for all invoices with open balances using IPP but I keep on getting 0 results back. Am I doing something wrong in code?

Here is my C# code snippet that I am trying to do with the applied Filtering

        InvoiceQuery qboInvoiceQuery = new InvoiceQuery();
        int iMaxPageNumber = QUERY_MAX_PAGE_NUMBER;   // A Constant with the Maximum Page Number allowed in a query 
        int iResultsPerPage = QUERY_MAX_NUM_PER_PAGE_INVOICE; // A Constant with the Maximum Results per page 

        // Paging Information
        qboInvoiceQuery.PageNumber = QUERY_START_PAGE_NUMBER;
        qboInvoiceQuery.ResultsPerPage = iResultsPerPage;

        #region Query Filtering
        //////////////////////////////////////////////
        //   initial filtering via Query Criteria   //
        //////////////////////////////////////////////
        // Get only Open (Unpaid) Invoices
        qboInvoiceQuery.OpenBalance = (decimal)0.00;
        qboInvoiceQuery.SpecifyOperatorOption(FilterProperty.OpenBalance, FilterOperatorType.AFTER);
        //////////////////////////////////////////////
        // END initial filtering via Query Criteria //
        //////////////////////////////////////////////
        #endregion

        // Complete the Query calls to build the list
        IEnumerable<Invoice> results = qboInvoiceQuery.ExecuteQuery<Invoice>(_ServiceContext);
        IEnumerable<Invoice> qboInvoices = results;
        int iCount = results.Count();
        while (iCount > 0 && iCount == iResultsPerPage && qboInvoiceQuery.PageNumber <= iMaxPageNumber)
        {
            qboInvoiceQuery.PageNumber++;
            results = qboInvoiceQuery.ExecuteQuery<Invoice>(_ServiceContext);
            iCount = results.Count();
            qboInvoices = qboInvoices.Concat(results);
        }

*** UPDATE ***

I have implemented peterl's answer and now have the following code. However I am now running into a new problem that my code is always returning the default of 10 invoices and is not taking into consideration my body. Even if i set it to a different page number or ResultsPerPage value I was get back the first page and 10 results. Any ideas?

    private Dictionary<string, Invoice> GetUnpaidInvoicesDictionary(IdType CustomerId, bool bById = true)
    {
        Dictionary<string, Invoice> dictionary = new Dictionary<string, Invoice>();
        int iMaxPageNumber = 100;
        int iResultsPerPage = 100;

        try
        {
            OAuthConsumerContext consumerContext = new OAuthConsumerContext
            {
                ConsumerKey = _sConsumerKey,
                SignatureMethod = SignatureMethod.HmacSha1,
                ConsumerSecret = _sConsumerSecret
            };

            string sBaseURL = "https://oauth.intuit.com/oauth/v1";
            string sUrlRequestToken = "/get_request_token";
            string sUrlAccessToken = "/get_access_token";
            OAuthSession oSession = new OAuthSession(consumerContext, 
                                                        sBaseURL + sUrlRequestToken,
                                                        sBaseURL,
                                                        sBaseURL + sUrlAccessToken);

            oSession.AccessToken = new TokenBase
            {
                Token = _sAccessToken,
                ConsumerKey = _sConsumerKey,
                TokenSecret = _sAccessTokenSecret
            };

            int iPageNumber = QUERY_START_PAGE_NUMBER;
            string sCustomerId = CustomerId.Value;
            string sBodyBase = "PageNum={0}&ResultsPerPage={1}&Filter=OpenBalance :GreaterThan: 0.00 :AND: CustomerId :EQUALS: {2}";
            string sBody = String.Format(sBodyBase, iPageNumber, iResultsPerPage, sCustomerId);

            IConsumerRequest conReq = oSession.Request();
            conReq = conReq.Post().WithRawContentType("application/x-www-form-urlencoded").WithRawContent(System.Text.Encoding.ASCII.GetBytes(sBody)); ;
            conReq = conReq.ForUrl(_DataService.ServiceContext.BaseUrl + "invoices/v2/" + _DataService.ServiceContext.RealmId);
            conReq = conReq.SignWithToken();



            // Complete the Query calls to build the list
            SearchResults searchResults = (SearchResults)_DataService.ServiceContext.Serializer.Deserialize<SearchResults>(conReq.ReadBody());
            IEnumerable<Invoice> results = ((Invoices)searchResults.CdmCollections).Invoice;
            IEnumerable<Invoice> qboInvoices = results;
            int iCount = searchResults.Count;
            while (iCount > 0 && iCount == iResultsPerPage && iPageNumber <= iMaxPageNumber)
            {
                iPageNumber++;

                sBody = String.Format(sBodyBase, iPageNumber, iResultsPerPage, sCustomerId);
                conReq = oSession.Request();
                conReq = conReq.Post().WithRawContentType("application/x-www-form-urlencoded").WithRawContent(System.Text.Encoding.ASCII.GetBytes(sBody)); ;
                conReq = conReq.ForUrl(_DataService.ServiceContext.BaseUrl + "invoices/v2/" + _DataService.ServiceContext.RealmId);
                conReq = conReq.SignWithToken();

                searchResults = (SearchResults)_DataService.ServiceContext.Serializer.Deserialize<SearchResults>(conReq.ReadBody());
                results = ((Invoices)searchResults.CdmCollections).Invoice;
                qboInvoices = qboInvoices.Concat(results);
                iCount = searchResults.Count;
            }

            if (bById)
                foreach (Invoice Inv in qboInvoices)
                    dictionary.Add(Inv.Id.Value, Inv);
            else
                foreach (Invoice Inv in qboInvoices)
                    dictionary.Add(Inv.Header.DocNumber, Inv);

            return dictionary;

        }
        catch (Exception)
        {

            return null;
        }
    }

* UPDATE *

There is a similar issue out there that involves the new api tester. This could be related to this issue and they are currently looking into it.

Stack Overflow: QuickBooks Online querying with filter returns 401 everytime

Answer 1

This is a bug in the DevKit. The OpenBalance filter defaults to :EQUALS: and does not support :GreaterThan:.

https://ipp.developer.intuit.com/0010_Intuit_Partner_Platform/0050_Data_Services/0400_QuickBooks_Online/Invoice#Attributes_Supporting_Filtering_and_Sorting

Here is a workaround using DevDefined to construct the OAuth header:

public List<Intuit.Ipp.Data.Qbo.Invoice> GetQboUnpaidInvoices(DataServices dataServices, int startPage, int resultsPerPage,  IdType CustomerId)
{
    StringBuilder requestXML = new StringBuilder();
    StringBuilder responseXML = new StringBuilder();

    var requestBody = String.Format("PageNum={0}&ResultsPerPage={1}&Filter=OpenBalance :GreaterThan: 0.00 :AND: CustomerId :EQUALS: {2}", startPage, resultsPerPage, CustomerId.Value);

    HttpWebRequest httpWebRequest = WebRequest.Create(dataServices.ServiceContext.BaseUrl + "invoices/v2/" + dataServices.ServiceContext.RealmId) as HttpWebRequest;
    httpWebRequest.Method = "POST";
    httpWebRequest.ContentType = "application/x-www-form-urlencoded";
    httpWebRequest.Headers.Add("Authorization", GetDevDefinedOAuthHeader(httpWebRequest, requestBody));
    requestXML.Append(requestBody);
    UTF8Encoding encoding = new UTF8Encoding();
    byte[] content = encoding.GetBytes(requestXML.ToString());
    using (var stream = httpWebRequest.GetRequestStream())
    {
        stream.Write(content, 0, content.Length);
    }
    HttpWebResponse httpWebResponse = httpWebRequest.GetResponse() as HttpWebResponse;
    using (Stream data = httpWebResponse.GetResponseStream())
    {
        Intuit.Ipp.Data.Qbo.SearchResults searchResults = (Intuit.Ipp.Data.Qbo.SearchResults)dataServices.ServiceContext.Serializer.Deserialize<Intuit.Ipp.Data.Qbo.SearchResults>(new StreamReader(data).ReadToEnd());
        return ((Intuit.Ipp.Data.Qbo.Invoices)searchResults.CdmCollections).Invoice.ToList();
    }

}

protected string GetDevDefinedOAuthHeader(HttpWebRequest webRequest, string requestBody)
{

    OAuthConsumerContext consumerContext = new OAuthConsumerContext
    {
        ConsumerKey = consumerKey,
        ConsumerSecret = consumerSecret,
        SignatureMethod = SignatureMethod.HmacSha1,
        UseHeaderForOAuthParameters = true

    };

    consumerContext.UseHeaderForOAuthParameters = true;

    //URIs not used - we already have Oauth tokens
    OAuthSession oSession = new OAuthSession(consumerContext, "https://www.example.com",
                            "https://www.example.com",
                            "https://www.example.com");


    oSession.AccessToken = new TokenBase
    {
        Token = accessToken,
        ConsumerKey = consumerKey,
        TokenSecret = accessTokenSecret
    };

    IConsumerRequest consumerRequest = oSession.Request();
    consumerRequest = ConsumerRequestExtensions.ForMethod(consumerRequest, webRequest.Method);
    consumerRequest = ConsumerRequestExtensions.ForUri(consumerRequest, webRequest.RequestUri);
    if (webRequest.Headers.Count > 0)
    {
        ConsumerRequestExtensions.AlterContext(consumerRequest, context => context.Headers = webRequest.Headers);
        if (webRequest.Headers[HttpRequestHeader.ContentType] == "application/x-www-form-urlencoded")
        {
            Dictionary<string, string> formParameters = new Dictionary<string, string>();
            foreach (string formParameter in requestBody.Split('&'))
            {
                formParameters.Add(formParameter.Split('=')[0], formParameter.Split('=')[1]);
            }
            consumerRequest = consumerRequest.WithFormParameters(formParameters);
        }
    }

    consumerRequest = consumerRequest.SignWithToken();
    return consumerRequest.Context.GenerateOAuthParametersForHeader();
}

http://nuget.org/packages/DevDefined.OAuth