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 }
Powered by Revindex Wiki