46 lines
1.5 KiB
PowerShell
46 lines
1.5 KiB
PowerShell
$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() |