jqGrid with CRUD Options in Node-RED (MySQL)
This flow uses HTML template and jqGrid to display, insert, update, delete and search data. The grid relies on MySQL database and accessible at: http://<node-red>:<port>/index.
[{"id":"d433feb1.6365e","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/index","method":"get","upload":false,"swaggerDoc":"","x":90,"y":45,"wires":[["98b6cece.37871"]]},{"id":"98b6cece.37871","type":"template","z":"b9d518b0.fab2a8","name":"web-template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n <head>\n <!-- The jQuery library is a prerequisite for all jqSuite products -->\n <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/jquery.min.js\"></script> \n <!-- We support more than 40 localizations -->\n <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/i18n/grid.locale-en.js\"></script>\n <!-- This is the Javascript file of jqGrid --> \n <script type=\"text/ecmascript\" src=\"http://www.guriddo.net/demo/js/trirand/jquery.jqGrid.min.js\"></script>\n <!-- This is the localization file of the grid controlling messages, labels, etc.\n <!-- A link to a jQuery UI ThemeRoller theme, more than 22 built-in and many more custom -->\n <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://struts.jgeppert.com/struts2-jquery-grid-showcase/themes/showcase/jquery-ui.css\" />\n <!-- The link to the CSS that the grid needs -->\n <link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"http://www.guriddo.net/demo/css/trirand/ui.jqgrid.css\" />\n <meta charset=\"utf-8\" />\n <title>Demo CRUD with jqGrid</title>\n <script type=\"text/javascript\"></script>\n <link rel=\"stylesheet\" type=\"text/css\" href=\"http://www.guriddo.net/B1D671CF-E532-4481-99AA-19F420D90332/netdefender/hui/ndhui.css\" />\n </head>\n <body>\n <script type=\"text/javascript\" language=\"javascript\" src=\"http://www.guriddo.net/B1D671CF-E532-4481-99AA-19F420D90332/netdefender/hui/ndhui.js?0=0&0=0&0=0\"></script>\n <table id=\"jqGrid\"></table>\n <div id=\"jqGridPager\"></div>\n <script type=\"text/javascript\"> \n $(document).ready(function () {\n $(\"#jqGrid\").jqGrid({\n url: '/search',\n mtype: \"GET\",\n datatype: \"json\",\n colModel: [\n { label: 'ID', name: 'id', key: true, search: false, width: 75 },\n { label: 'Name', name: 'name', width: 150, editable: true, formoptions: { colpos: 1, rowpos: 1 } },\n { label: 'Last Name', name: 'lastname', width: 150, editable: true, edittype: \"select\", formoptions: { colpos: 1, rowpos: 2 },\n editoptions: {\n dataUrl:'/search/lastname',\n type:\"GET\",\n buildSelect: function(data) {\n var response = jQuery.parseJSON(data); //JSON data\n var s = '<select>';\n if (response && response.length) {\n s += '<option hidden=\"true\">--- Select Lastname ---</option>';\n for (var i = 0, l=response.length; i<l ; i++) {\n var id = response[i].id;\n var val = response[i].value; \n // You can concatenate ID or any other string here\n //For example: var ri = response[i].id + response[i].value; \n s += '<option value=\"'+id+'\">'+val+'</option>';\n }\n }\n return s + \"</select>\";\n } \n } \n },\n { label: 'Code', name: 'code', width: 150, editable: true, formoptions: { colpos: 2, rowpos: 1 } }, \n { label: 'Married', name: 'married', width: 150, align: \"center\", search: false, editable: true, edittype: \"checkbox\", formatter: \"checkbox\", editoptions: { value: \"1:0\" }, formoptions: { colpos: 2, rowpos: 2 } }\n ],\n width: 500,\n height: 260,\n rowNum: 10,\n loadonce: true,\n viewrecords: true,\n pager: \"#jqGridPager\"\n });\n \n $('#jqGrid').navGrid('#jqGridPager',\n // The buttons to appear on the toolbar of the grid\n { edit: true, add: true, del: true, search: true, refresh: true, view: true, position: \"left\", cloneToTop: true },\n \n // Options for the Edit Dialog\n {\n url: '/update',\n editCaption: \"The Edit Dialog\",\n recreateForm: true,\n\t\t\t\t\t //checkOnUpdate : true,\n\t\t\t\t\t //checkOnSubmit : true,\n\t\t\t\t\t beforeSubmit : function( postdata, form , oper) {\n\t\t\t\t\t\t if(confirm('Are you sure you want to update this information?') ) {\n\t\t\t\t\t\t\t // Do something\n\t\t\t\t\t\t \t return [true,'/update'];\n\t\t\t\t\t\t } else {\n\t\t\t\t\t\t\treturn [false, 'Update failed!'];\n\t\t\t\t\t\t }\n\t\t\t\t\t },\n afterSubmit: function () {\n $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n return [true];\n },\n closeAfterEdit: true,\n errorTextFormat: function (data) {\n return 'Error: ' + data.responseText\n }\n },\n // Options for the Add Dialog\n {\n url: '/insert',\n addCaption: \"Add Dialog\",\n afterSubmit: function () {\n $(this).jqGrid(\"setGridParam\", {datatype: 'json'});\n return [true];\n },\n closeAfterAdd: true,\n recreateForm: true,\n errorTextFormat: function (data) {\n return 'Error: ' + data.responseText\n }\n },\n // Options for the Delete Dialog\n { \n url: '/delete',\n errorTextFormat: function (data) {\n return 'Error: ' + data.responseText\n }\n });\n $(\"#jqGrid\").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true });\n });\n </script>\n </body>\n</html>","x":350,"y":45,"wires":[["3c1830eb.33b9"]]},{"id":"3c1830eb.33b9","type":"http response","z":"b9d518b0.fab2a8","name":"","statusCode":"","headers":{},"x":1070,"y":45,"wires":[]},{"id":"ef285826.0e9738","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/search","method":"get","upload":false,"swaggerDoc":"","x":100,"y":105,"wires":[["e396f56a.dcda38"]]},{"id":"e396f56a.dcda38","type":"function","z":"b9d518b0.fab2a8","name":"search-query","func":"//msg.topic=\"SELECT CONCAT('M00','',test.id) AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code,test.married AS married FROM test INNER JOIN lastname ON lastname.id = test.lastname ORDER BY test.id DESC\";\nmsg.topic=\"SELECT test.id AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code,test.married AS married FROM test INNER JOIN lastname ON lastname.id = test.lastname ORDER BY test.id DESC\";\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":105,"wires":[["9fe2ed80.2522c"]]},{"id":"458b0528.a4d98c","type":"http response","z":"b9d518b0.fab2a8","name":"","statusCode":"","headers":{},"x":1070,"y":105,"wires":[]},{"id":"d84f16f7.3d9698","type":"function","z":"b9d518b0.fab2a8","name":"search-response","func":"return msg;","outputs":1,"noerr":0,"x":735,"y":105,"wires":[["2b952c3a.f63884"]]},{"id":"2b952c3a.f63884","type":"json","z":"b9d518b0.fab2a8","name":"","property":"payload","action":"","pretty":false,"x":920,"y":105,"wires":[["458b0528.a4d98c"]]},{"id":"c91568f0.c3f2f8","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/insert","method":"post","upload":false,"swaggerDoc":"","x":100,"y":225,"wires":[["81bb8cdf.93e5b"]]},{"id":"e4b82091.b8e9a","type":"function","z":"b9d518b0.fab2a8","name":"insert-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":725,"y":225,"wires":[["c224cbcf.e3e718"]]},{"id":"81bb8cdf.93e5b","type":"function","z":"b9d518b0.fab2a8","name":"insert-query","func":"msg.topic=\"INSERT INTO test (name,lastname,code,married,timestamp) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.lastname + \"','\" + msg.payload.code + \"','\" + msg.payload.married + \"',CURRENT_TIMESTAMP)\";\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":225,"wires":[["50307401.f1297c"]]},{"id":"c224cbcf.e3e718","type":"http response","z":"b9d518b0.fab2a8","name":"","x":1070,"y":225,"wires":[]},{"id":"9929a22b.66471","type":"function","z":"b9d518b0.fab2a8","name":"lastname-query","func":"msg.topic=\"SELECT id, lastname as value FROM lastname\";\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":165,"wires":[["6ce2248e.9c889c"]]},{"id":"4bfc0ef6.e8511","type":"function","z":"b9d518b0.fab2a8","name":"lastname-response","func":"return msg;","outputs":1,"noerr":0,"x":735,"y":165,"wires":[["7b9f8d8b.c6d4e4"]]},{"id":"c70c0833.831ab8","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/search/lastname","method":"get","upload":false,"swaggerDoc":"","x":130,"y":165,"wires":[["9929a22b.66471"]]},{"id":"f92b07.fa8e84f8","type":"http response","z":"b9d518b0.fab2a8","name":"","statusCode":"","headers":{},"x":1070,"y":165,"wires":[]},{"id":"7b9f8d8b.c6d4e4","type":"json","z":"b9d518b0.fab2a8","name":"","property":"payload","action":"","pretty":false,"x":920,"y":165,"wires":[["f92b07.fa8e84f8"]]},{"id":"857ff60c.da9078","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/delete","method":"post","upload":false,"swaggerDoc":"","x":100,"y":345,"wires":[["5b19502.0bae0b"]]},{"id":"9a8b3fa.c0817c","type":"function","z":"b9d518b0.fab2a8","name":"delete-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":725,"y":345,"wires":[["4520409.a2751c"]]},{"id":"5b19502.0bae0b","type":"function","z":"b9d518b0.fab2a8","name":"delete-query","func":"msg.topic=\"DELETE FROM test WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":345,"wires":[["973e8aa6.834338"]]},{"id":"4520409.a2751c","type":"http response","z":"b9d518b0.fab2a8","name":"","x":1070,"y":345,"wires":[]},{"id":"b9aef47c.8f0938","type":"http in","z":"b9d518b0.fab2a8","name":"","url":"/update","method":"post","upload":false,"swaggerDoc":"","x":100,"y":285,"wires":[["930f03c1.94bd5"]]},{"id":"ece93a93.ebc878","type":"function","z":"b9d518b0.fab2a8","name":"update-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":735,"y":285,"wires":[["e51fe384.1db73"]]},{"id":"930f03c1.94bd5","type":"function","z":"b9d518b0.fab2a8","name":"update-query","func":"msg.topic=\"UPDATE test SET name='\" + msg.payload.name + \"', lastname='\" + msg.payload.lastname + \"', code='\" + msg.payload.code + \"', married='\" + msg.payload.married + \"' WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":285,"wires":[["c1934c38.6647d"]]},{"id":"e51fe384.1db73","type":"http response","z":"b9d518b0.fab2a8","name":"","x":1070,"y":285,"wires":[]},{"id":"9fe2ed80.2522c","type":"mysql","z":"b9d518b0.fab2a8","mydb":"d48d08d1.d221d8","name":"database","x":540,"y":105,"wires":[["d84f16f7.3d9698"]]},{"id":"6ce2248e.9c889c","type":"mysql","z":"b9d518b0.fab2a8","mydb":"d48d08d1.d221d8","name":"database","x":540,"y":165,"wires":[["4bfc0ef6.e8511"]]},{"id":"50307401.f1297c","type":"mysql","z":"b9d518b0.fab2a8","mydb":"d48d08d1.d221d8","name":"database","x":540,"y":225,"wires":[["e4b82091.b8e9a"]]},{"id":"c1934c38.6647d","type":"mysql","z":"b9d518b0.fab2a8","mydb":"d48d08d1.d221d8","name":"database","x":540,"y":285,"wires":[["ece93a93.ebc878"]]},{"id":"973e8aa6.834338","type":"mysql","z":"b9d518b0.fab2a8","mydb":"d48d08d1.d221d8","name":"database","x":540,"y":345,"wires":[["9a8b3fa.c0817c"]]},{"id":"d48d08d1.d221d8","type":"MySQLdatabase","z":"","host":"","port":"","db":"","tz":""}]
