Search

Contents

Revindex Storefront

Export Print

QuickBooks export sales order (Powershell)

You can use the following Powershell script to export your completed sales orders information to an IIF file suitable for importing into your QuickBooks software.

# QuickBooksSalesOrderExport.ps1
#
# This script will export all completed sales order information 
# to a QuickBooks IIF file.
######################################################################

# Configuration
######################################################################

param
(
    [parameter(Mandatory = $false)][string]$APIKey = 'xxxxxxxxx',
    [parameter(Mandatory = $false)][string]$APIUrl = 'http://my.com/.../Revindex.Dnn.RevindexStorefront/Api/Rest/V1/ServiceHandler.ashx?portalid=0',
    [parameter(Mandatory = $false)][string]$APIUsername = 'host',
    [parameter(Mandatory = $false)][string]$OutFile = 'C:\SalesOrders.iif',
    [parameter(Mandatory = $false)][DateTime]$StartDate = '2001-01-01',
    [parameter(Mandatory = $false)][DateTime]$StopDate = [DateTime]::Now,
    [string]$QBBankAccount = 'Bank account',
    [string]$QBIncomeAccount = 'Income account',
    [int]$NetworkTimeout = 30000
)


# Functions
######################################################################


# Function to help post HTTP request to web service
Function PostWebRequest([String] $url, [String] $data, [int] $timeout)
{    
    $buffer = [System.Text.Encoding]::UTF8.GetBytes($data)
    [System.Net.HttpWebRequest] $webRequest = [System.Net.WebRequest]::Create($url)
    $webRequest.Timeout = $timeout
    $webRequest.Method = "POST"
    $webRequest.ContentType = "application/x-www-form-urlencoded"
    $webRequest.ContentLength = $buffer.Length;

    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($buffer, 0, $buffer.Length)
    $requestStream.Flush()
    $requestStream.Close()

    [System.Net.HttpWebResponse] $webResponse = $webRequest.GetResponse()
    $streamReader = New-Object System.IO.StreamReader($webResponse.GetResponseStream())
    $result = $streamReader.ReadToEnd()
    return $result
}


# Start program
######################################################################

Try
{    
    # Create IIF file
    # http://support.quickbooks.intuit.com/support/Articles/HOW12778
    # http://www.qblittlesquare.com/2011/07/import-lists-into-quickbooks-with-iif/
    # Write headers
    ('"!TRNS","DATE","ACCNT","NAME","CLASS","AMOUNT","MEMO"') >> $OutFile
    ('"!SPL","DATE","ACCNT","NAME","AMOUNT","MEMO"') >> $OutFile
    ('"!ENDTRNS"') >> $OutFile

    # We need to construct our XML request using the parameter list
    $strRequest = "<?xml version='1.0' encoding='utf-8'?>
                    <request>
                      <version>1.0</version>
                      <credential>
                        <username>$APIUsername</username>
                        <apiKey>$APIKey</apiKey>
                      </credential>
                      <service>GetSalesOrdersByDateRange</service>
                      <parameters>
                        <startDate>$StartDate</startDate>
                        <stopDate>$StopDate</stopDate>
                      </parameters>
                    </request>"
                    
    # Execute the API call
    $xRequest = [Xml] $strRequest
    [Xml]$xResponse = PostWebRequest $APIUrl $xRequest.InnerXml $NetworkTimeout
    if ($xResponse.response.code -ne '2000')
    {
        Write-Host "Error executing GetSalesOrders. Response: " + $xResponse.response.code + ' ' + $xResponse.response.message
        return    
    }

    [System.Xml.XmlElement]$salesOrders = $xResponse.SelectSingleNode('/response/return/salesOrders')


    $qbIIF = @()
    foreach ($salesOrder in $salesOrders.SelectNodes('salesOrder')) 
    {                 
        # Export only completed orders
        if ($salesOrder.status -ne '4')
        {
            continue
        }
    
        ('"TRNS", "' + ([DateTime]$salesOrder.orderDate).ToString("yyyy-MM-dd") + '","' + $QBBankAccount.Replace('"', '""') + '","' + $salesOrder.billingFirstName.Replace('"', '""') + ' ' + $salesOrder.billingLastName.Replace('"', '""') + '","' + $salesOrder.totalAmount + '","SalesOrder",""') >> $OutFile
        
        ('"SPL", "' + ([DateTime]$salesOrder.orderDate).ToString("yyyy-MM-dd") + '","' + $QBIncomeAccount.Replace('"', '""') + '","' + $salesOrder.billingFirstName.Replace('"', '""') + ' ' + $salesOrder.billingLastName.Replace('"', '""') + '","' + (-([Decimal]$salesOrder.totalAmount)) + '",""') >> $OutFile
        
        ('"ENDTRNS"') >> $OutFile
    }
}
Catch
{
    Write-Output $_.Exception.Message
}

Comments

Add comment

Powered by Revindex Wiki