A couple of IDEA regex functions and some simple code snippets ►

◄ Project Zebra: Let me know when you find out...

If you know what Agresso is, you might want to do that. For some time we used SQL Image Viewer because Unit4 switched to storing documents as binary blobs in the database, but it's been all change since to file system storage. This scrap of VBScript should handle both newer storage mechanisms. It won't handle legacy encrypted Compello files, unfortunately. If you know how to handle those, please do get in touch.

This also assumes familiarity with tools such as https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Public objLaunch : Set objLaunch = CreateObject("WScript.Shell")
If Instr(1, WScript.FullName, "CScript", vbTextCompare) = 0 Then
objLaunch.Run "cscript " & chr(34) & WScript.ScriptFullName & chr(34), 1, False: WScript.Quit
End If

strFoldersOn = "\\servername\": strDBServer = "DBSERVNAME": strDB = "agresso": Set objFSO = CreateObject("Scripting.FileSystemObject")

If WScript.Arguments.Count = 0 Then
Wscript.Echo "Fetches Agresso invoice images if you have " & strFoldersOn & " share and [" & strDBServer & "].[dbo].[" & strDB & "] permissions, depending on where the images you want are stored, plus BCP installed for the latter. Images are placed in subfolders of a timestamped folder with a log file, in the filename format supplier\suppid_transno_page.ext": WScript.Echo "": WScript.Echo "Either supply a list of voucher refs or a .txt file with one per line.": Wscript.Echo "": Wscript.Echo "Syntax: cscript //nologo agrinv.vbs [transno] [transno] [transno] etc": Wscript.Echo "Syntax: cscript //nologo agrinv.vbs <filename>.txt": WScript.Quit
End If

strCurr = objFSO.GetAbsolutePathName("."): strTDir = CreateObject("WScript.Shell").ExpandEnvironmentStrings("%Temp%")

Set objOpts = objFSO.CreateTextFile(strTDir & "\bcp-raw-image-settings.txt"): objOpts.Write("I" & chr(13) & chr(10) & "0" & chr(13) & chr(10) & "0" & chr(13) & chr(10) & "" & chr(13) & chr(10) & "n"): objOpts.Close

Set objRegEx = CreateObject("VBScript.RegExp"): objRegEx.Global = True: objRegEx.IgnoreCase = True: objRegEx.Pattern = "[^A-Za-z0-9 ()',.!&_-]" 'allow some common filename-safe punctuation but otherwise play it safe

Dim dt: dt=now: strInvoicesFolder = year(dt) & "-" & right("0"&month(dt),2) & "-" & right("0"&day(dt),2) & "_" & right("0"&hour(dt),2) & "." & right("0"&minute(dt),2) & "." & right("0"&second(dt),2)

If objFSO.FolderExists(strCurr & "\" & strInvoicesFolder) = False Then objFSO.CreateFolder strCurr & "\" & strInvoicesFolder
Set objLog = objFSO.CreateTextFile(strCurr & "\" & strInvoicesFolder & "\invoices.txt")

If WScript.Arguments.Count = 1 And Right(LCase(Wscript.Arguments(0)),4)=".txt" Then
strInput = Wscript.Arguments(0)
If objFSO.FileExists(strInput) Then
Set objTextFile = objFSO.OpenTextFile(strInput, 1): strInvoices = "'fnord'"
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline: If Len(strNextLine)>1 Then strInvoices = strInvoices & ",'" & strNextLine & "'"
Loop
strInvoices = Replace(strInvoices, "'fnord'," ,"")
objLog.Write "Input file was " & strInput & chr(13) & chr(10) & chr(13) & chr(10)
Else
WScript.Echo "Input file could not be read.": WScript.Quit
End If
End If

If WScript.Arguments.Count > 1 Then
strInvoices = "'fnord'"
For Each strArg in Wscript.Arguments
strInvoices = strInvoices & ",'" & strArg & "'"
Next: strInvoices = Replace(strInvoices, "'fnord'," ,"")
objLog.Write "Input list was " & strInvoices & chr(13) & chr(10) & chr(13) & chr(10)
End If

strConnect = "Driver={SQL Server};SERVER=" & strDBServer & ";DATABASE=" & strDB & ";Trusted_Connection=True;":
Set objConn = CreateObject("ADODB.Connection"): objConn.CommandTimeout=120: objConn.Open strConnect: Set objRecordSet = objConn.Execute("SELECT rtrim(h.doc_system_id) AS doc_system_id, c.apar_id, s.apar_name, i.doc_index_2, p.page_no, rtrim(f.file_suffix) as file_suffix, p.last_update, p.file_guid, isnull(replace(l.file_base_loc+'\'+l.file_rel_loc,'webservice\E:\','" & strFoldersOn & "'),'') AS file_loc FROM adsindex i INNER JOIN adspage p ON p.doc_guid=i.doc_guid INNER JOIN adsdocument d ON d.doc_guid=p.doc_guid INNER JOIN adsfileinfo f ON f.file_guid=p.file_guid LEFT OUTER JOIN adsfilelocation l ON l.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 adsdoctypehead h ON h.doc_library='DU' AND h.doc_library=d.doc_library AND h.doc_type=d.doc_type 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(" & strInvoices & ") ORDER BY c.apar_id, i.doc_index_2, p.page_no")

Do While Not objRecordSet.EOF
strType = objRecordSet.Fields.Item("doc_system_id").Value: strName = objRecordSet.Fields.Item("apar_id").Value & "_" & objRecordSet.Fields.Item("doc_index_2").Value & "_" & objRecordSet.Fields.Item("page_no").Value: strExt="." & objRecordSet.Fields.Item("file_suffix").Value: strSuppName = objRegEx.Replace(objRecordSet.Fields.Item("apar_name").Value, "") & " (" & objRecordSet.Fields.Item("apar_id").Value & ")"
Wscript.Echo strType & ": " & strSuppName & "\" & strName & strExt
objLog.Write strType & ": " & strSuppName & "\" & strName & strExt & chr(13) & chr(10)
If objFSO.FolderExists(strCurr & "\" & strInvoicesFolder & "\" & strSuppName) = False Then objFSO.CreateFolder strCurr & "\" & strInvoicesFolder & "\" & "\" & strSuppName
strOutput = strCurr & "\" & strInvoicesFolder & "\" & strSuppName & "\" & strName
If strType = "COMPELLO" Then
Set objError = objFSO.CreateTextFile(strOutput & ".txt"): objError.Write("Compello images are encrypted and this script cannot read them."): objError.Close
End If
If strType = "FILESTORE" Then
objFSO.CopyFile objRecordSet.Fields.Item("file_loc").Value, strOutput & strExt
End If
If strType = "AGRESSOBLOB" Then
strFile = objRecordSet.Fields.Item("file_guid").Value
'using the raw options in a redirection input file prevents BCP adding an additional four byte header, which would make the files invalid
strFetch = "cmd /c bcp ""SELECT blob_image FROM adsfileblob WHERE file_guid = '" & strFile & "';"" queryout """ & strOutput & strExt & """ -T -S " & strDBServer & " -d " & strDB & " -C RAW<""" & strTDir & "\bcp-raw-image-settings.txt"""
Set objShell = WScript.CreateObject("WScript.Shell")
Return = objShell.Run(strFetch, 0, true)
End If
If objFSO.FileExists(strOutput & strExt) Then Wscript.Echo "--> fetched" Else Wscript.Echo "--> FAILED!" End If
If objFSO.FileExists(strOutput & strExt) Then objLog.Write "--> fetched" & chr(13) & chr(10) Else objLog.Write "--> FAILED!" & chr(13) & chr(10) End If
objRecordSet.MoveNext
Loop

objLog.Close: WScript.Quit

💬 Comments are off, but you can use the mail form to contact or see the about page for social media links.

BLOG  MORE  ABOUT  MAIL