My reviews: Lenovo Tab M9 and M8 (2023) ►
◄ Victorinox releases a load of new tool designs/models
Also known as Agresso or BusinessWorld. Previously it was straightforward to get document library items from the database or local file store with an on-premises deployment and back in the day a bit of VBScript would do that. These days you're more likely to be using hosted systems, or the web API deployed locally, and VBScript is giving way to PowerShell.
This gets indexing information from a database and file contents via an API. It may be possible to do everything with the API but we haven't gotten that far if so. The connecting system user requires a specific role/permissions to use the API, plus the domain user having access to a suitable SQL Server instance.
#Requires -Version 7.4
If (Get-Module -ListAvailable -Name sqlserver) { } else { Install-Module sqlserver -Confirm:$False -Force }
$parameters = $args -join ', '; If ( $parameters -ilike '*.txt' ) { $list = Get-Content -Path $parameters; $invoices = $list | Join-String -SingleQuote -Separator ',' } else { $invoices = $args | Join-String -SingleQuote -Separator ',' }
If ($parameters -eq '') { Write-Host; Write-Host 'Usage: ./get-invoices.ps1 voucher_no voucher_no voucher_no ...'; Write-Host 'Usage: ./get-invoices.ps1 invoices.txt'; Write-Host; Exit }
Write-Host $invoices; Write-Host
$results = Invoke-Sqlcmd -TrustServerCertificate -ServerInstance "INSTANCE" -Database "DATABASE" -Query "SET NOCOUNT ON; SELECT cast(c.apar_id as varchar) as apar_id, replace(replace(replace(replace(replace(s.apar_name,char(9),''),char(10),''),char(11),''), char(12),''),char(13),'') as apar_name, cast(i.doc_index_2 as varchar)+'_'+cast(p.page_no as varchar)+'.'+rtrim(f.file_suffix) as file_name, i.doc_guid FROM adsindex i INNER JOIN adspage p ON p.doc_guid=i.doc_guid INNER JOIN adsfileinfo f ON f.file_guid=p.file_guid LEFT OUTER JOIN ( SELECT DISTINCT apar_id, voucher_no FROM asuhistr WHERE client='DU' UNION SELECT apar_id, voucher_no FROM asutrans WHERE client='DU') c ON c.voucher_no=i.doc_index_2 LEFT OUTER JOIN asuheader s ON s.apar_id=c.apar_id AND s.client='DU' AND s.apar_type='P' WHERE i.doc_index_2 IN( $invoices ) ORDER BY c.apar_id, i.doc_index_2, p.page_no;"
$user = Read-Host "ERP7 user"; $password = Read-Host "Password" -AsSecureString; $cred = [PSCredential]::new($user, $password); Write-Host
$now = Get-Date -Format FileDateTime; $new_folder = New-Item -ItemType Directory -Path $now
$output_file = "$now\output.txt"; $new_file = New-Item -ItemType File -Name $output_file
Add-Content -Path $output_file -Value $invoices; Add-Content -Path $output_file -Value ''
ForEach ($result in $results) {
$guid = $result.doc_guid; $file_name = $result.file_name; $apar_id = $result.apar_id; $apar_name = $result.apar_name
$fetch = Invoke-RestMethod -AllowUnencryptedAuthentication -Method "Get" -Credential $cred -Uri "http://SERVERNAME/Unit4ERP-web-api/v1/documents/$guid";
$save_folder = "$now\$apar_name ($apar_id)"; $save_file = "$save_folder\$file_name"
$new_folder = New-Item -ItemType Directory -Force -Path $save_folder
[IO.File]::WriteAllBytes($save_file, [Convert]::FromBase64String($fetch.fileContent));
Write-Host $save_file; Add-Content -Path $output_file -Value $save_file
}
Write-Host
💬 Comments are off, but you can use the mail form to contact or see the about page for social media links.
