Hi Chris, I have so time today to work on the script and have some updates.
Chris70 wrote: ↑24 Jan 2020, 14:26
I tried this change and it seems to work beautifully on the log with this issue. This is the change I made:
Code: Select all
if ( pallet_prid && !pallet_quantity )
pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )
This is fine but is redundant as at this point there's always a product id due to the `master filter`.
Looking for just the missing quantity like this
if ( !pallet_quantity ) should work ( untested ).
I found these anomalous scans and patched the filters against them:
10:38:23 <-70255,RO00589595001,51032[CR]
13:12:32 <-71130,71130,RO00589535023[CR]
I overhauled the product id filter logic to a `white list` pattern ( from bad scans to valid scans ).
This future proofs against trying to add an additional filter for every unforeseen scan error possibility.
After extensive testing and getting inconsistent comparison data using the FC command and several MS Office comparison features, I ended up building my own.
My initial worry about a custom comparison function was that it would be slow and memory intensive.
After benchmarking, it seems to perform much better than expected with reliable results for the scripts specific needs.
Comparison is now streamlined and performed on the original output file and a secondary scan, rather than having to create/read another output file.
Anyway give this latest version a test run and let me know if you run into any issues.
Code: Select all
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Variables
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
LogFile := "C:\Users\chickman\Documents\terminal-01-02-2020.log" ; points to the log file
StdPltQty := A_ScriptDir "\Std pallet qty-loc.xlsx" ; points to the standard quantity file
OutputFile := A_ScriptDir "\Scan Log 01-02-2020.csv" ; sets output file pattern
CopyToFile := "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-02-2020.csv" ; sets batch file pattern sequence
Duration := DelayMinutes( 3 ) ; how many minutes to wait between loops
CSVHead := "Pallet ID,Quantity,Product ID,Loc. Code"
Loop 4 ; remove 4 to continue looping
{
ProcessLogFile( LogFile, StdPltQty, OutputFile, CopyToFile, CSVHead )
Sleep % Duration
}
ExitApp ; remove if continuing looping
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Functions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
ProcessLogFile( lF, sF, oF, bF, cH = "" )
{
static cycle := 0 ; 1st or 2nd cycle init
pallet_cnt := 0 ; pallet count init
;; check if log and/or location files exist
CheckSourceFiles( sF, lF )
Display( "Processing..." )
;; open workbook, return excel object
global xO := CreateExcelObject( sF )
lFn := GetFileName( lF ), oFn := GetFileName( oF )
;; reset output file
if FileExist( oF ) && !cycle
FileDelete % oF
;; assign array of regex needles for log line filter and data extraction
NeedleArray := [ "[-`,`t](?<quantity>\d{1,3})\D", "s)[-`,`t](?<prid>\d{4,5})(?!.+?[-`,`t]\d{4,5}[,\[])", "\D(?<spid>RO\d{8})\D" ]
;; read the log file and loop through each line, toggle cycle
Loop Read, % lF, % ( oF, cycle:=!cycle )
{
;; the log file line passes if it contains a pallet id, contains 1 product id and the pallet id isn't in the pallet id list
if ( RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) && A_LoopReadLine ~= NeedleArray.2 ) && !InStr( pid_list, pallet_pid )
{
pid_list .= pallet_pid, pallet_loc := "", pallet_cnt++
;; extract quantity, product id & short pallet id from each filtered line
For Each, Needle in NeedleArray
{
RegExMatch( A_LoopReadLine, Needle, pallet_ )
}
;; find location code based on product id
QtyRowObj := xO.Range( "A1" ).End( -4162 ).Find( pallet_prid, xO.Cells( 1 ), -4163, 2, 1, 1, 0, 0 )
Try
pallet_loc := xO.Range( "D" QtyRowObj.Row ).Value
Catch
w .= RTrim( "Product ID: " pallet_prid " Location Not Found!`n" )
;; grab quantity if it's missing ; *commented out* with short pallet id
if ( !pallet_quantity ) ; << untested! original >> if ( pallet_spid && !pallet_quantity )
pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )
;; extracted scan data
extr_scan := pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc
;; assigns display string
disp_str := RTrim( "Filtering Log: " lFn . ( pC := "`nExtracted Scans: " pallet_cnt )
. "`n-----`n" ( lP := "Pallet ID: " pallet_pid "`nQuantity: " pallet_quantity
. "`nProduct ID: " pallet_prid "`nLocation: " pallet_loc )
. e := ( w ? "`n-----`n" w : "" ) )
;; 1st run writes output file, 2nd checks/retrieves updates
if cycle
{
Display( disp_str )
FileAppend % ( pallet_cnt = 1 && cH ? cH "`n" : "" ) . extr_scan "`n"
}
else
{
Display( "Checking For Log Updates..." . "`nLog File: " lFn "`n-----`nCurrent Scan: " extr_scan )
log_updates := GetLogUpdates( oF, extr_scan )
}
}
}
KillExcel()
;; updates from the log are saved to batch if any
if ( log_updates )
{
GetLogUpdates( "reset" )
Display( "Log File Changed!`nCreating Batch!" )
CreateBatchSequence( bF, cH "`n" log_updates )
}
else
{
Display( "Log Filter Complete!`nLog File: " lFn "`nOutput CSV: " oFn . pC "`n----- Last Pallet -----`n" lP . e )
}
}
CreateBatchSequence( bF, data )
{
SplitPath bF, FileName, Dir, Ext, NameNoExt
if FileExist( bFP := Dir "\" NameNoExt "_*." Ext )
{
Loop Files, % bFP
bfNum := A_LoopFileDir "\" NameNoExt "_" SubStr( ( fn := A_LoopFileName )
, InStr( fn, "_" )+1, -( StrLen( NameNoExt )-1 ) )+1 "." Ext
}
else
bfNum := Dir "\" NameNoExt "_1." Ext
if ( !FileExist( Dir ) )
{
FileCreateDir % Dir
}
FileAppend % data, % bfNum
bCnt := StrSplit( data, "`n" ).Length()-1
Display( "Batch File Created!`n-----`nBatch File: " GetFileName( bfNum ) "`nUpdate Count: " bCnt )
}
GetLogUpdates( oF, pS = "" )
{
static cL
if ( oF = "reset" )
{
cL := ""
}
else
{
Loop Read, % oF
{
if ( pS = A_LoopReadLine, match := false )
{
match := true
break
}
}
if ( match = false )
{
cL .= pS "`n"
}
return RTrim( cL, "`n" )
}
}
CreateExcelObject( sF, visible := false )
{
if ( !IsObject( xO ) )
{
Try
{
xO := ComObjCreate( "Excel.Application" ), xO.Visible := visible
OnExit( "KillExcel" ), xO.Workbooks.Open( sF )
}
Catch Error
{
MsgBox, 0x10, Error!, Cannot open an Excel instance!`nScript will now exit.
ExitApp
}
return xO
}
}
KillExcel()
{
global xO
if ( !xO.Visible )
DetectHiddenWindows, On
if ( xO.Workbooks.Count )
xO.Workbooks.Close
( xO.Quit ), xO := ""
}
CheckSourceFiles( sF, lF )
{
s := FileExist( sF ), l := FileExist( lF )
if (!s||!l)
{
MsgBox, 0x10, Error!, % "Cannot locate the " ( (b:=!s&&!l) ? "Log & the Standard Quantity"
: !s ? "Standard Quantity" : !l ? "Log" : "" ) " file" (b?"s":"") "!`nPoint to or install "
. (b?"them":"it") " & try again!"
ExitApp
}
}
Display( msg = "" )
{
if ( A_CoordModeToolTip != "Screen" )
CoordMode ToolTip, Screen
ToolTip % msg, 10, 10
}
GetFileName( p )
{
return ( a:=StrSplit( p, "\" ) )[ a.Length() ]
}
DelayMinutes( dur )
{
mis:=1000, min:=(60*mis)
return (dur*min)
}
BTW I've been reading the log files so much than I'm getting use to the file structure