$xml = [xml](get-content C:\Users\ewpmni\AppData\Local\Temp\28\Temp1_Finance-NonPersistent-AX-13184-20171030-N.zip\Finance-NonPersistent-AX-13184-20171030-N.xml)

$invoices = $xml.DataExportFile.DataGroup.DataType.Finance.Invoice | where { $_.creationtype -eq 1 }

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'Server=rejk-p-sql006\sql01;Database=TravelCardDynamicsAX;Trusted_Connection=True;'
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()

$sqlCommand.CommandText = @"
        SELECT
            INVOICEID
            , INVOICEDATE
            , NAME
            , SALESID
        FROM [TravelCardDynamicsAX].[dbo].[CUSTINVOICETRANS]
        where 
            invoiceid in ('$($invoices.documentuniqueid -join "','")') and invoicedate = '$($invoices[0].DocumentDate)'
"@

$reader = $sqlCommand.ExecuteReader()

if(-not $reader.HasRows) { Write-Warning "No rows found for DocumentUniqueID: $($invoice.DocumentUniqueID)"; continue }

$results = while($reader.Read()) {

    $result = [pscustomobject]@{
        invoiceId = $reader['INVOICEID']
        invoiceDate = $reader['INVOICEDATE']
        name = $reader['NAME']
        salesid = $reader['SALESID']
    }

    Write-Verbose -Verbose "ID: $($result.invoiceid)"
    
    Write-Output $result

    if($result.name -match 'Manual|Manuel') {
        Write-Warning "$($result.invoiceid) name: $($result.name)"
    }
}

$reader.Close()

$sqlCommand.Dispose()
$sqlConnection.Close()