This blog post highlights the features and usage of a WinBatch Utility I created years ago - TDSQRW
- SQR Background Processor / Analyzer. It provides an Easy-to-Use GUI Interface that selects an SQR program & easily executes it directly via the SQRW.exe background executable. This is an alternate means of running a program outside of PeopleSoft Process Scheduler. The utility is also useful for analyzing SQR source code (including embedded SQC routines) & formatting the output in Excel. Perfect for documenting large, complex processes. Here's some of the features:
SQR Background
Processor Functions:
1 – Extract all PeopleSoft profiles (Configuration Manager)
for selection.
2 – Ability to alter Configuration settings (temporary for
application only).
3 – Execute SQRW utilizing correct set of parameters.
4 – Analyze SQR Source Code (Includes, Procedures, Logic Flow, SQL)
Analyzer Functions
include:
1 – INCLUDE – All Include files are expanded and listed.
2 – ROUTINES - Every
Procedure is listed with Module of origin and called routines.
3 – FLOWCHART – Process Flowchart created highlighting
procedural logic.
4 – SQL – SQL statements extracted and listed with
Procedure/Module of origin (i.e. SQR,SQC).
All sheets are automatically generated with appropriate Report
Headings, Freeze Pane, AutoFilter, Colorization, etc…
System Requirements
and Assumptions:
A directory named C:\TEMP is assumed to exist (All generated
EXCEL output is placed in this folder). PeopleSoft is installed via client
setup to utilize profile selection (process may still run without this in
place). Microsoft Excel must be installed for the Analyzer - an Excel session opens up automatically & populates four worksheet tabs with the analysis output.
MAIN Screen:
Use the File List Box to select the SQR source code file. To
execute via <SUBMIT> the ID, Password and Database are required. Select a
Profile and hit <REFRESH> or <EDIT> to update manually – this will
ensure the appropriate SQR search variables and executable location used.
<ANALYZE> will evaluate the selected SQR program (no ID/Password/Database
required).
The fish graphic is a watercolor I painted a while ago (it can be replaced with anything named TDSQRW.BMP). At work this utility was simply known as "The Fish"... as in "run through Process Scheduler or the Fish".
Configuration (via Refresh or Edit):
The environment variables set according to the configuration
manager profile may be altered using the Configuration Screen (Accessed by
using the <REFRESH> or <EDIT> functions.
View Command Line:
You may view the SQRW command line by pressing the
<VIEW> button. In this example you can see the USERID and PASSWORD have
not been selected yet.
Online Help:
Press the <HELP> button for some basic assistance…
SQR Analyzer:
INCLUDE Phase:
All #Include files are expanded utilizing the PSSQR1 thru PSSQR4 file search settings. All expanded files are listed on the first worksheet – an expanded source code file will reside in the C:\TEMP directory with a file extension of .SQZ.
ROUTINES Phase:
The ROUTINES phase will create a new worksheet with all
Procedures listed along with any called routines. The module of origin is also
listed. AutoFilter is automatically incorporated into the sheet.
FLOWCHART Phase:
The FLOWCHART phase produces an indented flowchart of the procedural logic of the program. Any repeated procedures will be terminated with <R> - this avoids redundancy and potential endless loops when deciphering the process logic.
SQL Phase:
All SQL statements are extracted, parsed appropriately and
displayed on the SQL worksheet. AutoFilter is very useful for pinpointing the
exact location of a SQL statement.
TDSQRW.WBT - Source Code Snippets:
Below you'll find some WinBatch Code snippets to illustrate how the application is written. Looks like I'll need to update the PeopleTools Registry key address since we're on a higher release. This allows you to access the various Configuration Profiles. Further down in the code you can see how the Button Selections are handled.
;**********************************************************************
;* PROCESS MAIN *
;**********************************************************************
gosub Define_Dialog_TD
; No Listbox Selection Required
IntControl(4,0,0,0,0)
; Restrict Multiple Listbox Selection (only one allowed)
IntControl(33,0,0,0,0)
ErrorMode(@OFF)
Profiles = ""
Rpath = "SOFTWARE\PeopleSoft\PeopleTools\Release8.40\Profiles"
Rkey = RegOpenKey(@REGMACHINE, Rpath)
Rerr = LastError()
if Rerr == 0
Profiles = RegQueryKeys(Rkey)
RegCloseKey(Rkey)
endif
ErrorMode(@CANCEL)
gosub Custom_Init
IDXdir = xSQR1
ErrorMode(@OFF)
DirChange(IDXdir)
ErrorMode(@CANCEL)
zMsg = "[Enter Parameters please]"
sw = @YES
while sw == @YES
zListBox = Profiles
zFileBox = DirGet()
zMsg = StrCat("PATH: ", zFileBox, " ", zMsg)
ButtonPushed=Dialog("TD")
zMsg = ""
Select ButtonPushed
Case 0
sw = @NO ; Documentation Only (See :CANCEL)
break
Case 1 ; Submit
gosub Process_Submit
break
Case 2 ; Refresh
gosub Process_Refresh
gosub Process_Edit
break
Case 3 ; Edit
gosub Process_Edit
break
Case 4 ; Analyze
gosub Process_Analyze
break
Case 5 ; Help
gosub Process_Help
break
Case 6
Case 7
Case 8
Case 9
sqrX = ButtonPushed - 5
sqrD = xSQR%sqrX%
ErrorMode(@OFF)
DirChange(sqrD)
ErrorMode(@CANCEL)
EndSelect
endwhile
:CANCEL
exit
;**********************************************************************
The Process_Analyze routine is the fun part of the program. This is where the source module text is sliced, diced, reassembled, parsed & flowcharted.
;**********************************************************************
;* Process Analyze *
;**********************************************************************
:Process_Analyze
wPGM = zFileBox
if wPGM == "" | wPGM == "NOFILESELECTED"
zMsg = "[Please select a program]"
else
timeRaw = StrReplace(TimeYmdHms(),":","")
timeYmd = StrSub(timeRaw,1,8)
timeHms = StrSub(timeRaw,9,6)
TDdate = StrCat(timeYmd,"_",timeHms)
XLobj = ObjectOpen("Excel.Application")
XLobj.DisplayAlerts = @FALSE
XLobj.Visible = @TRUE
XLobj.UserControl = @TRUE
WBS = XLobj.Workbooks
XLS = WBS.add
WS = XLobj.Worksheets
WKS = WS.add
WKS = WS.add
WKS = WS.add
PRTname = pGetDefPrtInf(1) ; Name Request
PRTport = pGetDefPrtInf(3) ; Port Request
PRTactive = "%PRTname% on %PRTport%"
wROOT = FileRoot(wPGM)
gosub Expand_Source
gosub Analyze_Routines
gosub SQR_Flowchart
gosub SQL_Extract
XLobj.UserControl = @FALSE
XLS.SaveAs("c:\temp\tdsqrw_%wROOT%_%TDdate%.xls")
XLobj.Quit
ObjectClose(WKS)
ObjectClose(WS)
ObjectClose(XLS)
ObjectClose(WBS)
ObjectClose(XLobj)
endif
Return
;**********************************************************************
The most interesting part of the Analyzer is creating the Process Flowchart. This is an indented Tree Structure that visually displays the Parent/Child relationship between a procedure or function & the procedures/functions that they may call.
;**********************************************************************
;* SQR Flowchart *
;**********************************************************************
:SQR_Flowchart
ctr = 1
TDXLS_SheetName(XLobj, 3, "FLOWCHART")
TDXLS_SheetActivate(XLobj,3)
TDXLS_FormatColumn(XLobj, 1, 30, "@")
col = 2
while col <= 20
TDXLS_FormatColumn(XLobj, col, 3, "@")
col = col + 1
endwhile
TDXLS_WriteCell(XLobj, ctr, 1, @TRUE, @TRUE, 0, "V", "Module")
TDXLS_WriteCell(XLobj, ctr, 3, @TRUE, @TRUE, 0, "V", "Process Flowchart")
TDXLS_ColorRow(XLobj, ctr, 1, 23, 36)
ctr = ctr + 2
; Flowchart BEG...
S_ptr = ArrDimension(30)
S_sub = ArrDimension(30)
S_idx = 0
while S_idx < 30
S_ptr [S_idx] = 0
S_sub [S_idx] = ""
S_idx = S_idx + 1
endwhile
S_idx = 1
while S_idx > 0
Z_idx = S_ptr [S_idx]
Z_sub = S_sub [S_idx]
Y_proc = A_proc [Z_idx]
Y_sub = A_sub [Z_idx]
Y_tag = A_tag [Z_idx]
Y_type = A_type [Z_idx]
Y_mod = A_mod [Z_idx]
Z_ptr = Z_idx
Z_idx = Z_idx + 1
S_ptr [S_idx] = Z_idx
Y_procU = StrUpper(Y_proc)
Z_subU = StrUpper(Z_sub)
Z_go = @FALSE
if Y_procU == PROC_S & S_idx == 1
Z_go = @TRUE
endif
if Y_procU == PROC_H & S_idx == 1
Z_go = @TRUE
endif
if Y_procU == PROC_F & S_idx == 1
Z_go = @TRUE
endif
if Y_procU == PROC_R & S_idx == 1
Z_go = @TRUE
endif
if Y_procU == Z_subU & Y_proc <> ""
Z_go = @TRUE
endif
if Z_go == @TRUE
if Y_tag == 0
I_sub = Y_sub
if Y_type == K_error
I_sub = StrCat("On-Error=", I_sub)
endif
col = S_idx + 2
TDXLS_WriteCell(XLobj, ctr, 1, @FALSE, @FALSE, 0, "V", Y_mod)
TDXLS_WriteCell(XLobj, ctr, col, @FALSE, @FALSE, 0, "V", I_sub)
SVrow = ctr
SVcol = col
ctr = ctr + 1
S_idx = S_idx + 1
Z_idx = 0
S_ptr [S_idx] = Z_idx
S_sub [S_idx] = Y_sub
else
if Y_tag > 0
I_sub = TDXLS_GetValue(XLobj, SVrow, SVcol, @FALSE)
I_sub = StrCat(I_sub, " <R>")
TDXLS_WriteCell(XLobj, SVrow, SVcol, @FALSE, @FALSE, 0, "V", I_sub)
endif
Z_idx = A_idx + 1
endif
A_tag [Z_ptr] = Y_tag + 1
endif
if Z_idx > A_idx
S_idx = S_idx - 1
endif
endwhile
; Flowchart END
TDXLS_Zoom(XLobj, 70)
TDXLS_Orientation(XLobj, "P")
TDXLS_GridLines(XLobj, @TRUE)
TDXLS_PaperSize(XLobj, 1) ; Letter (Default)
TDXLS_AutoFilter(XLobj, 2, 1, ctr, 1)
TDXLS_FreezePane(XLobj, 3, 1)
TDXLS_TitleRows(XLobj, 1, 2)
TDXLS_HeaderFooter(XLobj, "H", "L", "TDSQRW - %wROOT% (%TDdate%) - FLOWCHART")
TDXLS_HeaderFooter(XLobj, "H", "R", "NSS/Advance Publications [TD]")
TDXLS_HeaderFooter(XLobj, "F", "L", "Page &P of &N")
TDXLS_HeaderFooter(XLobj, "F", "R", "&D &T")
Return
;**********************************************************************
The Excel Integration is handled by a custom copybook I wrote - TDXLS.WBC. It includes routines to perform various XLS functions - each starting with the prefix TDXLS_%. Functions are listed below with a short description:
;**********************************************************************
;* *
;* MODULE: TDXLS.WBC *
;* AUTHOR: TONY DELIA. *
;* DATE: 03/18/2004. *
;* DESC: WINBATCH MICROSOFT EXCEL SUPPORT. *
;* VERSION: WINBATCH 2003J. *
;* *
;**********************************************************************
;* *
;* OVERVIEW: THIS MODULE CONTAINS ROUTINES TO ACCESS MICROSOFT *
;* EXCEL OBJECTS/PROPERTIES/METHODS. THIS INCLUDES THE *
;* RETRIEVAL/ASSIGNMENT OF CELL VALUES, CELL XLATION, *
;* COLORIZATION AND CELL/COLUMN FORMATTING. ADDITIONAL *
;* FUNCTIONALITY WILL BE IMPLEMENTED SOON. *
;* *
;**********************************************************************
;* *
;* ROUTINES: TDXLS_GetValue() - Extract Cell Value. *
;* TDXLS_GetFormula() - Extract Cell Formula. *
;* TDXLS_WriteCell() - Write Cell Value/Formula. *
;* TDXLS_ColumnXlate() - Numeric-to-Alpha Col Xlt. *
;* TDXLS_ColorRow() - Color Row. *
;* TDXLS_FormatCell() - Format Cell. *
;* TDXLS_FormatColWrap() - Format Column. *
;* TDXLS_AutoFit() - Autofit Column(s). *
;* TDXLS_AutoFitRows() - Autofit Row(s). *
;* TDXLS_TitleRows() - Title Rows Page Setup. *
;* TDXLS_Orientation() - Landscape/Portrait. *
;* TDXLS_Zoom() - Zoom Page Size (%). *
;* TDXLS_HeaderFooter() - Set Header/Footer. *
;* TDXLS_PrintArea() - Set PrintArea. *
;* TDXLS_GridLines() - Print Grid Lines. *
;* TDXLS_PaperSize() - Set Paper Size. *
;* TDXLS_ActivePrinter() - Set Active Printer. *
;* TDXLS_FreezePane() - Freeze Window Pane. *
;* TDXLS_SheetName() - Assign Sheet Name. *
;* TDXLS_SheetActivate() - Activate Sheet. *
;* TDXLS_AutoFilter() - Set AutoFilter for Row. *
;* *
;**********************************************************************
Below is an example of one of the XLS functions - TDXLS_AutoFilter(). Each function is concisely documented listing the Input/Output parameters.
;**********************************************************************
;* AUTOFILTER FUNCTION *
;**********************************************************************
;* *
;* INPUT: I_XLS - XL Object *
;* I_rowX - X-Coordinate Row *
;* I_colX - X-Coordinate Column *
;* I_rowY - Y-Coordinate Row *
;* I_colY - Y-Coordinate Column *
;* OUTPUT: n/a - None (Set to @TRUE) *
;* *
;**********************************************************************
#DefineFunction TDXLS_AutoFilter(I_XLS, I_rowX, I_colX, I_rowY, I_colY)
colX = TDXLS_ColumnXlate(I_colX)
colY = TDXLS_ColumnXlate(I_colY)
myRange = I_XLS.Range("$%colX%$%I_rowX%:$%colY%$%I_rowY%")
myRange.Select
mySel = I_XLS.Selection
mySel.AutoFilter
ObjectClose(mySel)
ObjectClose(myRange)
return @TRUE
#EndFunction
;**********************************************************************
Notice all code is streamlined, clean & crisp, well-documented & efficient... I do not permit a single line of sloppy code... not under any circumstance... ever! We'll end this post on that note...
