Forums >> Programming >> Open Source >>
The viewItems Application




Posted:
bvstone

The viewItems Application

 
The viewItems Application

The Node.js application

Following is the viewItems.js application:

var express = require('express');
var util = require('util');
var myStuff = require('mymodules');
var router = express.Router();

var DBname = "*LOCAL";
var Library = 'BVSTONES';
var Table = 'ITEMPF';

router.all('/', function(req, res, next) {
  myStuff.setDBname(DBname);
  myStuff.setDBschema(Library);
  myStuff.setDBtable(Table);

	var sql = "select rrn(a) AS RRN, a.* from %s.%s a";
  sql = util.format(sql, Library, Table);
  
  var resultSet = myStuff.getResultSet(sql);
  var columnList = myStuff.getFieldDescriptions();
  var fieldTypes = myStuff.getFieldTypes();
  
  res.render('viewItems', {resultSet: resultSet, columnList: columnList, fieldTypes: fieldTypes});
});

module.exports = router;

The viewItems.js node.js program is used to present a list of items to the user.  As with any node.js application, we start out with a few "require" statements to allow the use of functionality included in the available modules.  

  • express - Required to use the Express framework
  • util - A nice string replacement module
  • mymodules - This will be explained in detail later, but this includes a set of functions I created specifically to deal with IBM i data
  • router - Again, required for the Express framework

You may be asking where is the /QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2 require statement?  Well, because we encapsulated the database access, update and insert functionality into our own module (mymodules) we don't need to require it here.

Examining the rest of the application we see it's actually quite simple.  We first define the table, library and database name we want to use.  We use a couple of "setters" to set this information inside of the myStuff (mymodules) module.  We then build a normal SQL statement and call three of our home-grown functions:

  • getResultSet() - Passing the SQL statement into this function will return the result set.  An example of the result set is as follows:
     
    [ { RRN: '1',
        ITITEM: 'BLUECAR',
        ITIDESC: 'Light Blue Car',
        ITPRICE: '1000.00',
        ITQTY: '1' },
      { RRN: '2',
        ITITEM: 'HAMSAND',
        ITIDESC: 'Ham Sandwich',
        ITPRICE: '1.29',
        ITQTY: '1' }, ...
    ]

     
  • getFieldDescriptions() - This function returns an object containing the descriptions for each column/field in the file we are querying.  The object is returned in the following format:
    {column_name:column_text, column_name:column_text...}

    An example of the field descriptions object is as follows:
     
    { ITITEM: 'Item Number', 
      ITIDESC: 'Desc', 
      ITPRICE: 'Price', 
      ITQTY: 'Qty' }

    This means that using the column name we can get the column description using the format column_text = object[column_name].  We will see this in use later in the application.
     
  •  getFieldTypes() - This function returns an object containing the field types for each column/field in the file we are querying.  The object returned is similar to the object returned by the getField Descriptions() function:
    {column_name:field_type, column_name:field_type...}

    Knowing the field type (ie, character, date, numeric, etc) will be helpful when we are building the HTML form.  An example of the field type object is as follows:
     
    { ITITEM: 'text',
      ITIDESC: 'text',
      ITPRICE: 'numeric',
      ITQTY: 'numeric' }

     

Once we have everything we need, we pass the result set, column list and list of field types to our template. 

The Jade/Pug Template

Following is the viewItems.jade template:

extends layout

block content
  - var dontShow = ['RRN']
  - var replaceHeadings = {ITITEM: 'Item Number', ITIDESC: 'Description', ITPRICE: 'Price', ITQTY: 'Qty'} 

  .updatableDiv
    | To add a new item, fill in the top row and click the Update button.
    include ./mixins/dataList.jade
    +dataList('detail', true)

Yes, it is this small.  Why?  Just as with eRPG applications I've been finding little hidden treasures that allow me to break up the pages into smaller reusable parts (ie, Server Side Includes).  But, in the case of the Jade template engine they are called Includes and Mixins.

Includes allow you to include pre-created code into your template.  Mixins take that one step further and work sort of like functions in that you can pass in variables to the Mixin in order to change the result of the data that is shown.

Two other important things to mention are the dontShow array and the replaceHeadings object.  Any column name we add to the dontShow array will be omitted from our display.  We can also override the column names from the system using the replaceHeadings object, as we have in this case.  Both of these allow us to keep some of the display functionality in the template vs the node.js application.  This of course means that if we want to modify the columns shown or the column headings we can do so without needed to restart our application.  

So, let's take a look at the dataList.jade Include file which includes the dataList Mixin:

mixin dataList(listID, showNewRecord)
  .dataList
    p= resultSet.length + " rows returned."
    table
      tr    
        each fieldValue, fieldKey in columnList
          if (dontShow.indexOf(fieldKey) < 0)
            th.small1(align="left") 
              if (!replaceHeadings[fieldKey])
                =columnList[fieldKey]
              else
                =replaceHeadings[fieldKey]
      if (showNewRecord)
        tr(data-rrn=0, data-ID=listID)
          each fieldValue, fieldKey in columnList
            if (dontShow.indexOf(fieldKey) < 0)
              td.small1 
                include showFields.jade
                +showFields(fieldTypes[fieldKey], fieldKey, "")
      if (resultSet.length)  
          each result in resultSet
            tr(data-rrn=result.RRN, data-ID=listID)
              each fieldValue, fieldKey in result
                if (dontShow.indexOf(fieldKey) < 0)
                  td.small1 
                    include showFields.jade
                    +showFields(fieldTypes[fieldKey], fieldKey, fieldValue)
 
    br
    button.updateButton Update
    
    script.
      adjustFieldSizes(10);

Now, if you're not familiar with the Jade template engine that would be a good place to start learning.  It's a method that makes creating HTML on the fly "easier".  I only put quotes around "easier" because in order to know how to use the template engine there is a prerequisite of knowing HTML.  So you're still not getting away from that (as well as JavaScript, which obviously needs to be understood for node.js programming).  Once you know the HTML then you need to learn the syntax and functionality of the template engine you're using.  

For this Mixin we pass in 2 parameters:

  • listID - This is simply a unique ID that we will use to assign an ID to each row of data.  This is important for when we want to update records.
  • showNewRecord - This is a boolean parameter that allows us to show a blank record or not.  This is useful in situations where not only we allow updating of existing records, but where we want to allow the user to enter new records.

We are also using the 3 objects, resultSet, columnList and fieldTypes from our node.js application.  Now, these "could" be passed in as parameter but as long as we call them the same names in our application they don't need to be.

First we use the resultSet parameter and the length property to display how many rows of data there are.

    p= resultSet.length + " rows returned."

We then start our table.  Normally we want to include headings for each column and this case is no different.  This is where the columnList object comes into play.  We loop through each of the headings in the columnList object and check to see if the column name is in the dontShow array.  If not, we display the column heading.  Of course, we also check the replaceHeadings object for any override of the heading.

    table
      tr    
        each fieldValue, fieldKey in columnList
          if (dontShow.indexOf(fieldKey) < 0)
            th.small1(align="left") 
              if (!replaceHeadings[fieldKey])
                =columnList[fieldKey]
              else
                =replaceHeadings[fieldKey]

Next, we check the boolean value of the showNewRecord parameter to see if we need to display a blank record used for adding new records to the file.

      if (showNewRecord)
        tr(data-rrn=0, data-ID=listID)
          each fieldValue, fieldKey in columnList
            if (dontShow.indexOf(fieldKey) < 0)
              td.small1 
                include showFields.jade
                +showFields(fieldTypes[fieldKey], fieldKey, "")

Finally we get to the nitty gritty.  This next section is where we will loop through the result set and display a row for each of the records in the result set.  We first check to make sure there are entries in the result set using the length property of resultSet.  Again, before including each column we check the dontShow array to make sure we want to show this particular column.  

      if (resultSet.length)  
          each result in resultSet
            tr(data-rrn=result.RRN, data-ID=listID)
              each fieldValue, fieldKey in result
                if (dontShow.indexOf(fieldKey) < 0)
                  td.small1 
                    include showFields.jade
                    +showFields(fieldTypes[fieldKey], fieldKey, fieldValue)

Wait... what's this?  Another Mixin?

Yes, we're using another Mixin (showFields) to display the actual contents of the table cell.  So, lets take a look at the showFields.jade Mixin template:

mixin showFields(inFieldType, inKey, inValue)
  .showFields
    if (inFieldType == 'numeric')
      input.inKey.adjustable(name=fieldKey, value=inValue, type="number")
    else if ((inFieldType == 'date') || (inFieldType == 'timestamp'))
      if (inValue == '')
        - var newValue = '0001-01-01'
      else
        - var newValue = inValue
      input.inKey.datepicker.adjustable(name=fieldKey, value=newValue.substr(0,10), type="text")
    else
      textarea.inKey.adjustable(name= fieldKey, cols=10, rows=1)
        | #{inValue}

The showFields Mixin accepts 3 parameters:

  • The field type (inFieldType)
  • The field name (inKey)
  • The field value (inValue)

Now, depending on the type of field we create an input field of the appropriate type (ie, number, textbox, or text for a date).  If a field is a date we use a jQuery UI addon to make selecting dates easier called datepicker.  This particular example doesn't have any date data types, so for now we'll not worry about that.

Returning back to the dataList.jade template we see that after the table is complete we display a simple update button and then call a JavaScript function named adjustFieldSizes().  

    br
    button.updateButton Update
    
    script.
      adjustFieldSizes(10);

The adjustFieldSizes() function is included in the client side javascript and is used to adjust the size of each of the fields used depending on their contents.

function adjustFieldSizes(textSize) {
  $('textarea,adjustable').each(function(index) {
    var length = 0;
    var textArray = $(this).text().split(/\n/);
    var rows = textArray.length;

    for (var i = 0; i < rows; i++) {
      var thisLength = textArray[i].length;

      if (thisLength > length) {
        length = thisLength;
      }
    }
    
    if (length <= 0) length=textSize;
    if (rows <= 0) rows = 1;
    
    $(this).attr('cols', length + 5);
    $(this).attr('rows', rows);
  });
  
  $('input[type=text].adjustable').each(function(index) {
    var length = $(this).text().length;

    if (length <= 0) length=textSize;
      
    $(this).attr('size', $(this).val().length);
  });
}

Now that we have a grasp of how things are displayed, the next article will look at how we allow the application to update and add records.


Last edited 05/10/2016 at 10:08:22


Reply




Copyright 1983-2017 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).