Terminal ETL Engine
Green Screens ETL engine is a simple but powerful engine to extract and transform screen data to generic JSON format based on JSON based mapping definitions. Later, such data can be converted to any required form like we did internally to generate Word and Excel documents directly inside web browser.
ETL engine works in coordination with screen parser API to extract screen data into generic JSON form as a first step.
Use this simple example to extract current screen to JSON format.
var obj = Tn5250.Application.screenExtract();
console.log(obj);
Extracted screen JSON data is in format as shown below. (variable screen in final example at the bottom of the page)
{
row : {
col : 'value'
}
}
This format allow us to easily map and retrieve screen data as format contains all information about screen text positions and segments.
TIP: Use ALT+A to reveal screen attributes showing screen segments.
Next is ETL mapping definition which contains base data extraction mappings. Here is base structure just to get the picture. (look at def variable in final example at the bottom of the page)
{
// map field order id to name
"fields": {
"command": 10
},
// map and extract screen positions [row, col, length]
"data": {
"title": [0, 29,21]
},
// map subfile data extraction
"grids": {
"wrkactjob": {
// macro engine definition for subfile scroll
"macro" : null,
// subfile definition, number of rows and record height
"def": {
"rows": 9,
"rowHeight": 1
},
// column definitions for header, first record, totals
"headers": [[8,6,13]],
"data": [[9,6,13]],
"totals": []
}
}
}
To extract and convert active screen, combine ETL extract with D-API to retrieve screen definition resource from server.
io.greenscreens.ETLController.getDefinition('wrkactjob.json', function(res,def,sts) {
var screen = Tn5250.Application.screenExtract();
var result = Tn5250.ETL.extract(def, screen, false);
console.log(result);
});
And finally, offline example with detailed extract definition for the purpose of this blog.
var def = {
"templates": {
"default" : {
"excel": "wrkactjob.xlsx",
"word": "wrkactjob.docx",
"output": "wrkactjob",
"script": null
}
},
"excel": {
"sheet": "Sheet1",
"fields": {
"title": ["C1","Sheet1"],
"system": { "cell": "B3", "style": {"bold": true} },
"date": "D3",
"time": ["F3"],
"jobs": "F2",
"test1": { "row": 1, "col": 1, "sheet": "Book1", "ref": "test"},
"test2": { "cell": "G9", "sheet": "Book1", "style": {"bold": true} }
},
"grids": {
"wrkactjob": {
"sheet": "Sheet1",
"header": ["A5", "B5", "C5", "D5", "E5", "F5"],
"detail": ["A6", "B6", "C6", {"cell": "D6", "style": {}, "type": "number"}, "E6", "F6" ],
"total": {"style": {"bold": true }}
},
"wrkactjob1": {
"ref": "wrkactjob",
"sheet": "Sheet2",
"header": { "row": 5, "col": 1 },
"detail": { "row": 6, "col": 1 },
"total": { "row": 16, "col": 1, "style": { "bold": true }, "offset": false }
}
}
},
"fields": {
"command": 10
},
"data": {
"title" : [0,29,21],
"system" : [0,71,8],
"date" : [1,61,8],
"time" : [1,71,8],
"jobs" : [2,62,4],
"test": "fixed data",
"created": {
"exp": "(new Date()).toLocaleDateString(navigator.languages[0] || navigator.language).replace(/ /g,'');",
"fn": ""
}
},
"grids": {
"wrkactjob": {
"macro": [
{
"cmd": "PGDOWN",
"startTrigger": { "r" : 18, "c":72, "t":"More..."},
"stopTrigger": {"r" : 18, "c": 73, "t": "Bottom"},
"processTrigger": {"r" : 8, "c": 6, "t": "Subsystem/Job"}
}
],
"def": {
"rows": 9,
"rowHeight": 1
},
"headers": [ [8, 6, 13], [8, 21, 11], [8, 33, 5], [8, 39, 5], [8, 46, 15], [8, 62, 8], "const" ],
"data": [[9, 6, 13], [9, 21, 11], [9, 33, 5], [9, 39, 5], [9, 46, 15], [9, 63, 8], "Fixed" ],
"totals": [ "t1", "t2", "t3", "=SUM(INDIRECT(ADDRESS(6,COLUMN())&\":\"&ADDRESS(ROW()-1,COLUMN())))", "t5", "t6", "t7" ]
}
}
};
var screen = {"0":{"29":"Work with Active Jobs","71":"PUB400"},"1":{"57":"17-04-19","66":"11:25:59 UTC"},"2":{"1":"CPU %:","10":".0","20":"Elapsed time:","36":"00:00:00","49":"Active jobs:","64":"264"},
"4":{"1":"Type options, press Enter."},"5":{"3":"2=Change","14":"3=Hold","23":"4=End","31":"5=Work with","45":"6=Release","57":"7=Display message"},
"6":{"3":"8=Work with spooled files","31":"13=Disconnect ..."},"7":{"21":"Current"},"8":{"1":"Opt","6":"Subsystem/Job","21":"User","33":"Type","39":"CPU %","46":"Function","62":"Status"},
"9":{"6":"#SYSLOAD","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},"10":{"6":"SYSLOAD","21":"#SYSLOAD","33":"ASJ","39":".0","46":"DLY-300","63":"DLYW"},
"11":{"6":"FB400","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},"12":{"6":"QBATCH","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},"13":{"6":"PUB400USS","21":"RZKHWORK","33":"PJ","39":".0","46":"DLY-14","63":"DLYW"},
"14":{"6":"QBATCH2","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},"15":{"6":"VEERENDRA","21":"VEERENDRA","33":"BCH","39":".0","46":"PGM-MSG","63":"MSGW"},"16":{"6":"QCMN","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},
"17":{"6":"QCTL","21":"QSYS","33":"SBS","39":".0","63":"DEQW"},"18":{"67":"More..."},"19":{"1":"Parameters or command"},"20":{"1":"===>"},"21":{"1":"F3=Exit","11":"F5=Refresh","28":"F7=Find","41":"F10=Restart statistics"},
"22":{"1":"F11=Display elapsed data","28":"F12=Cancel","41":"F23=More options","60":"F24=More keys"}
};
var data = Tn5250.ETL.extract(def, screen, false);
console.log(data);
After Tn5250.ETL.extract, data JSON variable will contain exported and transformed screen data in generic format.
{
"title": "Work with Active Jobs",
"system": "PUB400",
"date": "",
"time": "",
"jobs": "",
"test": "fixed data",
"created": "4/28/2017",
"wrkactjob": [
["#SYSLOAD","QSYS","SBS",0,"","","Fixed"],
["SYSLOAD","#SYSLOAD","ASJ", 0,"DLY-300","","Fixed"],
["FB400","QSYS","SBS",0,"","","Fixed"],
["QBATCH","QSYS","SBS",0,"","","Fixed"],
["PUB400USS","RZKHWORK","PJ",0,"DLY-14","","Fixed"],
["QBATCH2","QSYS", "SBS",0,"","","Fixed"],
["VEERENDRA","VEERENDRA","BCH",0,"PGM-MSG","","Fixed"],
["QCMN","QSYS","SBS",0,"","","Fixed"],
["QCTL","QSYS","SBS",0,"","","Fixed"]
]
}
And finally, let us give you small teaser to think about it. If you are using some of modern web frameworks to create SPA web applications, shown data format is the most common format used to render modern UI. With Green Screens Web Terminal great integration features, it is possible to integrate web terminal, etl engine and web frameworks to modernize classic green screens applications. How to do this, we will describe in one of our future posts. Until then, use this new knowledge and learn features of ETL engine.