Skip to content

Instantly share code, notes, and snippets.

@RainerRoss

RainerRoss/Machine01.sqlrpgle

Last active June 6, 2019 11:29
Show Gist options
  • Save RainerRoss/c6978c74fbc78dab747d49614797bca4 to your computer and use it in GitHub Desktop.
Save RainerRoss/c6978c74fbc78dab747d49614797bca4 to your computer and use it in GitHub Desktop.
Download ZIP
MyFirstApp
Raw
Machine01.sqlrpgle
ctl-opt dftactgrp(*no) alloc(*teraspace) option(*nodebugio:*nounref)
main(main) actgrp('MACHINE') bnddir('WEBSRVUTL');
//------------------------------------------------------------------//
// //
// Webservice Maschinen JSON-Format with JSON_ARRAYAGG //
// //
//----------------- //
// R.Ross 03.2019 * //
//------------------------------------------------------------------//
// Prototypes //
//------------------------------------------------------------------//
/copy websrvutl/qcpysrc,websrvutl
/copy websrvutl/qcpysrc,apierr
/copy websrvutl/qcpysrc,sqloptions
//------------------------------------------------------------------//
// File Arrays //
//------------------------------------------------------------------//
dcl-ds DsMachine extname('MACHINE00') qualified alias end-ds;
//------------------------------------------------------------------//
// Variables //
//------------------------------------------------------------------//
dcl-s GblJson SQLType(CLOB:1000000) ccsid(*utf8);
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc main;
dcl-s LocHeader like(GblHeader); // HTTP-Header
LocHeader = getHeader(); // Get HTTP-Header
clear GblJson; // JSON-Data
crtJson(); // Create JSON-Data
wrtStdout(%addr(LocHeader:*data):%len(LocHeader):DsApierr);
if GblJson_Len > *zero;
wrtStdout(%addr(GblJson_Data):GblJson_Len:DsApierr);
endif;
end-proc;
//------------------------------------------------------------------//
// Create JSON-Data - all Countries //
//------------------------------------------------------------------//
dcl-proc crtJson;
exec sql
select JSON_OBJECT(
'items' value
JSON_ARRAYAGG(
JSON_OBJECT(
'id' value id,
'name' value trim(name)
)
)
)
into :GblJson
from machine00;
sqlcode = sqlcode; // Debug
end-proc;
//------------------------------------------------------------------//
Raw
Machine02.sqlrpgle
ctl-opt dftactgrp(*no) alloc(*teraspace) option(*nodebugio:*nounref)
main(main) actgrp('MACHINE') bnddir('WEBSRVUTL');
//------------------------------------------------------------------//
// //
// Webservice Maschinen - Insert //
// //
//----------------- //
// R.Ross 03.2019 * //
//------------------------------------------------------------------//
// Prototypes //
//------------------------------------------------------------------//
/copy websrvutl/qcpysrc,websrvutl
/copy websrvutl/qcpysrc,apierr
/copy websrvutl/qcpysrc,sqloptions
//------------------------------------------------------------------//
// Array Files //
//------------------------------------------------------------------//
dcl-ds DsMachine extname('MACHINE00') qualified alias end-ds;
//------------------------------------------------------------------//
// Array Status //
//------------------------------------------------------------------//
dcl-ds DsStatus qualified; // Status
Success varchar(05) inz('true'); // Success
Errmsg char(256); // ErrorMessage
end-ds;
//------------------------------------------------------------------//
// Array Input-Data //
//------------------------------------------------------------------//
dcl-ds DsData qualified;
Id like(DsMachine.Id);
Name like(DsMachine.Name);
end-ds;
//------------------------------------------------------------------//
// Variables //
//------------------------------------------------------------------//
dcl-s GblJson SQLType(CLOB:10000) ccsid(*utf8);
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc main;
dcl-s LocHeader like(GblHeader);
reset DsStatus; // Status
clear DsData; // Input-Data
LocHeader = getHeader(); // Get HTTP-Header
getInput(); // Get HTTP-Input
DsData.Id = %dec(getKeyValue('id'):10:0);
DsData.Name = getKeyValue('name');
crtJson(DsData);
wrtStdout(%addr(LocHeader:*data):%len(LocHeader):DsApierr);
if GblJson_Len > *zero;
wrtStdout(%addr(GblJson_Data):GblJson_Len:DsApierr);
endif;
end-proc;
//------------------------------------------------------------------//
// Create JSON-Data //
//------------------------------------------------------------------//
dcl-proc crtjson;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
insertData(PiData);
exec sql
values JSON_OBJECT(
'success' value trim(:DsStatus.Success) Format JSON,
'errmsg' value trim(:DsStatus.Errmsg)
)
into :GblJson;
end-proc;
//------------------------------------------------------------------//
// Insert Data //
//------------------------------------------------------------------//
dcl-proc InsertData;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
exec sql
insert into MACHINE00
(Id, Name)
values (:PiData.Id, :PiData.Name)
with nc;
if sqlcode < *zero;
DsStatus.Success = 'false';
DsStatus.Errmsg = 'SQLCode = ' + %char(sqlcode);
endif;
end-proc;
//------------------------------------------------------------------//
Raw
Machine03.sqlrpgle
ctl-opt dftactgrp(*no) alloc(*teraspace) option(*nodebugio:*nounref)
main(main) actgrp('MACHINE') bnddir('WEBSRVUTL');
//------------------------------------------------------------------//
// //
// Webservice Maschinen - Change //
// //
//----------------- //
// R.Ross 03.2019 * //
//------------------------------------------------------------------//
// Prototypes //
//------------------------------------------------------------------//
/copy websrvutl/qcpysrc,websrvutl
/copy websrvutl/qcpysrc,apierr
/copy websrvutl/qcpysrc,sqloptions
//------------------------------------------------------------------//
// Array Files //
//------------------------------------------------------------------//
dcl-ds DsMachine extname('MACHINE00') qualified alias end-ds;
//------------------------------------------------------------------//
// Array Status //
//------------------------------------------------------------------//
dcl-ds DsStatus qualified; // Status
Success varchar(05) inz('true'); // Success
Errmsg char(256); // ErrorMessage
end-ds;
//------------------------------------------------------------------//
// Array Input-Data //
//------------------------------------------------------------------//
dcl-ds DsData qualified;
Id like(DsMachine.Id);
Name like(DsMachine.Name);
end-ds;
//------------------------------------------------------------------//
// Variables //
//------------------------------------------------------------------//
dcl-s GblJson SQLType(CLOB:10000) ccsid(*utf8);
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc main;
dcl-s LocHeader like(GblHeader);
reset DsStatus; // Status
clear DsData; // Input-Data
LocHeader = getHeader(); // Get HTTP-Header
getInput(); // Get HTTP-Input
DsData.Id = %dec(getKeyValue('id'):10:0);
DsData.Name = getKeyValue('name');
crtJson(DsData);
wrtStdout(%addr(LocHeader:*data):%len(LocHeader):DsApierr);
if GblJson_Len > *zero;
wrtStdout(%addr(GblJson_Data):GblJson_Len:DsApierr);
endif;
end-proc;
//------------------------------------------------------------------//
// Create JSON-Data //
//------------------------------------------------------------------//
dcl-proc crtjson;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
changeData(PiData);
exec sql
values JSON_OBJECT(
'success' value trim(:DsStatus.Success) Format JSON,
'errmsg' value trim(:DsStatus.Errmsg)
)
into :GblJson;
end-proc;
//------------------------------------------------------------------//
// Change Data //
//------------------------------------------------------------------//
dcl-proc ChangeData;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
exec sql
update MACHINE00
set Name = :PiData.Name
where Id = :PiData.Id
with nc;
if sqlcode < *zero;
DsStatus.Success = 'false';
DsStatus.Errmsg = 'SQLCode = ' + %char(sqlcode);
endif;
end-proc;
//------------------------------------------------------------------//
Raw
Machine04.sqlrpgle
ctl-opt dftactgrp(*no) alloc(*teraspace) option(*nodebugio:*nounref)
main(main) actgrp('MACHINE') bnddir('WEBSRVUTL');
//------------------------------------------------------------------//
// //
// Webservice Maschinen - Delete //
// //
//----------------- //
// R.Ross 03.2019 * //
//------------------------------------------------------------------//
// Prototypes //
//------------------------------------------------------------------//
/copy websrvutl/qcpysrc,websrvutl
/copy websrvutl/qcpysrc,apierr
/copy websrvutl/qcpysrc,sqloptions
//------------------------------------------------------------------//
// Array Files //
//------------------------------------------------------------------//
dcl-ds DsMachine extname('MACHINE00') qualified alias end-ds;
//------------------------------------------------------------------//
// Array Status //
//------------------------------------------------------------------//
dcl-ds DsStatus qualified; // Status
Success varchar(05) inz('true'); // Success
Errmsg char(256); // ErrorMessage
end-ds;
//------------------------------------------------------------------//
// Array Input-Data //
//------------------------------------------------------------------//
dcl-ds DsData qualified;
Id like(DsMachine.Id);
end-ds;
//------------------------------------------------------------------//
// Variables //
//------------------------------------------------------------------//
dcl-s GblJson SQLType(CLOB:10000) ccsid(*utf8);
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc main;
dcl-s LocHeader like(GblHeader);
reset DsStatus; // Status
clear DsData; // Input-Data
LocHeader = getHeader(); // Get HTTP-Header
getInput(); // Get HTTP-Input
DsData.Id = %dec(getKeyValue('id'):10:0);
crtJson(DsData);
wrtStdout(%addr(LocHeader:*data):%len(LocHeader):DsApierr);
if GblJson_Len > *zero;
wrtStdout(%addr(GblJson_Data):GblJson_Len:DsApierr);
endif;
end-proc;
//------------------------------------------------------------------//
// Create JSON-Data //
//------------------------------------------------------------------//
dcl-proc crtjson;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
deleteData(PiData);
exec sql
values JSON_OBJECT(
'success' value trim(:DsStatus.Success) Format JSON,
'errmsg' value trim(:DsStatus.Errmsg)
)
into :GblJson;
end-proc;
//------------------------------------------------------------------//
// Delete Data //
//------------------------------------------------------------------//
dcl-proc DeleteData;
dcl-pi *n;
PiData likeds(DsData) const;
end-pi;
exec sql
delete MACHINE00
where Id = :PiData.Id
with nc;
if sqlcode < *zero;
DsStatus.Success = 'false';
DsStatus.Errmsg = 'SQLCode = ' + %char(sqlcode);
endif;
end-proc;
//------------------------------------------------------------------//
Raw
MyApp.css
.center {
text-align: center;
}
.right {
text-align: right;
}
.left {
text-align: left;
}
.app_button button {
padding: 0;
text-align: center;
}
.circle_button button {
text-align: center;
}
.myhover {
background: #e8e8e8;
}
.highlighted_header.header6 {
background-color: #fbfbfb !important;
}
Raw
MyFirstApp.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>MyFirstApp</title>
<link rel="stylesheet" href="/codebase/webix/codebase/webix.min.css">
<link rel="stylesheet" href="/codebase/fontawesome/css/all.css">
<link rel="stylesheet" href="/tms/css/myapp.css">
</head>
<body>
<script type="text/javascript" src="/codebase/webix/codebase/webix.min.js"></script>
<script type="text/javascript" src="/tms/js/myfirstapp.js"></script>
</body>
</html>
Raw
MyFirstApp.js
//------------------------------------------------------------------------------//
// Application Object //
//------------------------------------------------------------------------------//
function Application() {
this.crud = ""; // chg/cpy/add
}
Application.prototype = {
setCrud: function(type) {
this.crud = type;
},
getCrud: function() {
return this.crud;
},
print: function() {
console.log("Application: " + JSON.stringify(this, 0, 1));
}
};
//------------------------------------------------------------------------------//
// MyApplication Object //
//------------------------------------------------------------------------------//
var MyApp = new Application();
//------------------------------------------------------------------------------//
// Toolbar //
//------------------------------------------------------------------------------//
var toolbarColumns = [
{
view:"label",
label:"MyFirstApp!"
},
{
view:'button',
type:"iconButton",
icon:"fas fa-plus-circle",
label:"Neuanlage",
autowidth:true,
align:"left",
click:'addCustomer("customerWindow", "customerForm");'
},
{
view:'button',
type:"iconButton",
icon:"fas fa-sync",
label:"Refresh",
autowidth:true,
align:"left",
click:"readData('myTable');"
},
{
view:"button",
type:"iconButton",
icon:"fas fa-file-excel",
label:"Excel Export",
width:160,
align:"left",
click:"webix.toExcel($$('myTable'));"
},
{
view:"button",
type:"iconButton",
icon:"fas fa-file-csv",
label:"CSV Export",
width:160,
align:"left",
click:"webix.toCSV($$('myTable'));"
}
];
var editIcon = {text:"<span class='webix_icon wxi-pencil'></span>", css:'center'};
var trashIcon = {text:"<span class='webix_icon wxi-trash'></span>", css:'center'};
var mapIcon = {text:"<span class='webix_icon fas fa-map-marker-alt'></span>", css:'center'};
//------------------------------------------------------------------------------//
// Columns //
//------------------------------------------------------------------------------//
var customerColumns = [
{id:"id", header:"Id", width:70, sort:"int"},
{id:"name", header:["Name", {content:"textFilter"}], fillspace:true, sort:"string"},
{id:"country", header:["Land", {content:"multiSelectFilter"}], width:100, sort:"string"},
{id:"zip", header:["PLZ", {content:"textFilter"}], width:80, sort:"string"},
{id:"city", header:["Ort", {content:"textFilter"}], width:130, sort:"string"},
{id:"street", header:["Straße", {content:"textFilter"}], width:160, sort:"string"},
{id:"sales", header:["Umsatz", {content:"numberFilter"}], width:120, sort:"int", css:"right", format:webix.i18n.numberFormat},
{id:"credit", header:["Kredit", {content:"numberFilter"}], width:100, sort:"int", css:"right", format:webix.i18n.numberFormat},
{id:"balance", header:["Saldo", {content:"numberFilter"}], width:100, sort:"int", css:"right", format:webix.i18n.numberFormat},
{id:"date", header:["Datum", {content:"datepickerFilter"}], width:130, sort:"date", css:"center",
format:function(value) {
return webix.i18n.dateFormatStr(value);
}
},
{id:"map", header:mapIcon, template:"<div class='webix_icon fas fa-map-marker-alt'></div>", width:40, css:"center"},
{id:"edit", header:editIcon, template:"{common.editIcon()}", width:40, css:"center"},
{id:"trash", header:trashIcon, template:"{common.trashIcon()}", width:40, css:"center"}
];
//------------------------------------------------------------------------------//
// Loading Scheme //
//------------------------------------------------------------------------------//
var dateParserISO = webix.Date.strToDate("%Y-%m-%d");
var customerScheme = {
$init:function(obj) {
if (obj.date) {
obj.date = dateParserISO(obj.date);
}
}
};
//------------------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------------------//
webix.ready(function() {
webix.i18n.setLocale("de-DE");
webix.i18n.parseFormat = "%Y-%m-%d %H:%i:%s";
webix.i18n.setLocale();
webix.csv.delimiter.cols = ", ";
webix.ui({
rows:[
{
view:"toolbar",
css:"webix_dark",
elements:toolbarColumns
},
{
view:"datatable",
id:"myTable",
css:"webix_header_border webix_data_border",
columns:customerColumns,
hover:"myhover",
dragColumn:true,
resizeColumn:true,
scheme:customerScheme,
headermenu:{width:200}
}
]
});
webix.extend($$("myTable"), webix.ProgressBar);
addDatatableEvent("myTable");
readData("myTable");
});
//------------------------------------------------------------------------------//
// Add Datatable Event //
//------------------------------------------------------------------------------//
function addDatatableEvent(id) {
$$(id).attachEvent("onItemClick", function(id, e, node) {
var item = this.getItem(id);
//webix.message({text:"Click: " + id + " Col: " + id.column, expire:-1}); //xxx
//webix.message({text:'Item: ' + '<pre>' + JSON.stringify(item, null, 1) + '</pre>', expire:-1}); //xxx
switch(id.column) {
case "trash":
deleteItem(item);
break;
case "map":
showCustomerMap("customerMapWindow", "customerMap", item);
break;
case "edit":
editCustomer("customerWindow", "customerForm", item);
break;
}
});
}
//------------------------------------------------------------------------------//
// Add Customer //
//------------------------------------------------------------------------------//
function addCustomer(windowid, formid) {
MyApp.setCrud('add');
MyApp.print(); //xxx
$$("customerWindowLabel").define("label", "Neuanlage");
$$("customerWindowLabel").refresh();
$$(windowid).getBody().clear();
$$(formid).clearValidation();
var newid = parseInt($$("myTable").getLastId(), 10) + 1;
$$(formid).setValues({
id: newid
});
$$(windowid).show();
$$(windowid).getBody().focus("name");
}
//------------------------------------------------------------------------------//
// Edit Customer //
//------------------------------------------------------------------------------//
function editCustomer(windowid, formid, item) {
MyApp.setCrud('chg');
MyApp.print(); //xxx
$$("customerWindowLabel").define("label", "Bearbeiten");
$$("customerWindowLabel").refresh();
$$(windowid).getBody().clear();
$$(formid).clearValidation();
$$(formid).setValues(item);
$$(windowid).show();
$$(windowid).getBody().focus("name");
}
//------------------------------------------------------------------------------//
// Form Rules //
//------------------------------------------------------------------------------//
var machineFormRules = {
name: function(value, obj) {
if (!webix.rules.isNotEmpty(value)) {
this.markInvalid("name", "Name darf nicht leer sein");
return false;
}
return true;
},
city: function(value, obj) {
if (!webix.rules.isNotEmpty(value)) {
this.markInvalid("city", "Ort darf nicht leer sein");
return false;
}
return true;
},
};
//------------------------------------------------------------------------------//
// Form Elements //
//------------------------------------------------------------------------------//
var customerFormElements = [
{view:"text", label:"Id", name:"id", readonly:true},
{view:"text", label:"Name", name:"name", required:true},
{view:"select", label:"Land", name:"country", required:true,
options:[
{id:"DE", value:"DE"},
{id:"IT", value:"IT"},
{id:"US", value:"US"},
]
},
{view:"text", label:"PLZ", name:"zip", required:true},
{view:"text", label:"ORT", name:"city", required:true},
{view:"text", label:"Strasse", name:"street", required:true},
{view:"text", label:"Umsatz", name:"sales"},
{view:"text", label:"Kredit", name:"credit"},
{view:"text", label:"Saldo", name:"balance"},
{view:"datepicker", label:"Datum", name:"date"},
{margin: 5,
cols:[
{view:"button", value:"Speichern", type:"form",
click:function(){
switch(this.getFormView().validate()) {
case true:
var item = this.getFormView().getValues();
//xxxwebix.message({text:'Item: ' + '<pre>' + JSON.stringify(item, null, 1) + '</pre>', expire:-1}); //xxx
if (MyApp.getCrud() === 'chg') {
$$("myTable").updateItem(item.id, item);
changeData(item);
}
if (MyApp.getCrud() === 'add') {
$$("myTable").add(item);
insertData(item);
}
this.getTopParentView().hide();
break;
}
}
},
{view:"button", value:"Abbrechen", click:function(){this.getTopParentView().hide();}}
]}
];
//------------------------------------------------------------------------------//
// Form //
//------------------------------------------------------------------------------//
var customerForm = {
view:"form",
id:"customerForm",
borderless: true,
elementsConfig:{
labelPosition:"left",
labelWidth:90
},
elements:customerFormElements,
rules:machineFormRules
};
//------------------------------------------------------------------------------//
// Window Head //
//------------------------------------------------------------------------------//
var customerWindowHead = {
view:"toolbar", cols:[
{view:"label", id:"customerWindowLabel", label:"Bearbeiten"},
{view:"icon", icon:"wxi-close", click:function(){this.getTopParentView().hide();}}
]
};
//------------------------------------------------------------------------------//
// Form Window //
//------------------------------------------------------------------------------//
webix.ui({
view:"window",
id:"customerWindow",
width:600,
position:"center",
move:true,
resize:true,
modal:true,
head:customerWindowHead,
body:customerForm
});
//------------------------------------------------------------------------------//
// Show Google Map Window //
//------------------------------------------------------------------------------//
function showCustomerMap(windowid, mapid, item) {
$$(mapid).clearAll(); // Delete all Markers
addMarker(mapid, item);
$$(windowid).show();
}
function addMarker(mapid, item) {
var markerData = {id:1, lat:51.224, lng:9.815, title:''}; // Marker Default
// markerData.lat = item.latitude; // Marker Latitude
// markerData.lng = item.longitude; // Marker Longitude
markerData.title = "Kunde: " + item.name;
$$(mapid).add(markerData);
}
//------------------------------------------------------------------------------//
// Google Map Window //
//------------------------------------------------------------------------------//
webix.ui({
view:"window",
id:"customerMapWindow",
move:true,
resize:true,
position:"center",
width:600,
height:600,
head:{
view:"toolbar", elements:[
{view:"label", label:"Google Map", align:"left"},
{view:"icon", icon:"wxi-close", css:"circle_button", click:function() {this.getTopParentView().hide();}}
]
},
body:{
view:"google-map",
id:"customerMap",
zoom:10,
center:[51.224, 9.815],
key:"AIzaSyAi0oVNVO-e603aUY8SILdD4v9bVBkmiTg"
}
});
//------------------------------------------------------------------------------//
// Delete Item //
//------------------------------------------------------------------------------//
function deleteItem(item) {
webix.confirm({
title: "Loeschen",
text: "Sind Sie sicher, dass Sie:</br><strong>" + item.name + "</strong></br>loeschen wollen?",
ok: "Ja",
cancel: "Abbrechen",
callback: function(result) {
if (result) {
$$("myTable").remove(item.id);
deleteData(item);
}
}
});
}
//------------------------------------------------------------------------------//
// Read Data //
//------------------------------------------------------------------------------//
function readData(id) {
$$(id).showProgress({hide:true});
webix.ajax().post("/tmslib/customer01.pgm", {id:0},
function(text, data) {
$$(id).clearAll();
$$(id).parse(data.json().items);
$$(id).setPage(0);
$$(id).hideProgress();
}
);
}
//------------------------------------------------------------------------------//
// Insert Data //
//------------------------------------------------------------------------------//
function insertData(item) {
webix.ajax().post("/tmslib/customer02.pgm", {id:item.id, name:item.name},
function(text, data) {
});
}
//------------------------------------------------------------------------------//
// Change Data //
//------------------------------------------------------------------------------//
function changeData(item) {
webix.ajax().post("/tmslib/customer03.pgm", {id:item.id, name:item.name},
function(text, data) {
});
}
//------------------------------------------------------------------------------//
// Delete Data //
//------------------------------------------------------------------------------//
function changeData(item) {
webix.ajax().post("/tmslib/customer04.pgm", {id:item.id},
function(text, data) {
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment