DataTables

Introduction

Sahana Eden uses the JavaScript library DataTables ​http://www.datatables.net to display results returned from the database. The DataTables library provides a lot of properties and functions that customise the table that is produced. Sahana Eden doesn't expose all of the functionality that is available within the DataTables library this makes it easy to get DataTables up and running and it also ensures a consistent look and feel. However, it is possible to customise the DataTables through the Sahana Eden framework.

Properties

A number of properties are set by the framework and rarely need to be changed. These have been marked in the following table as auto. Some properties are set with a default value, the default value (and where it is set) is indicated in the table below:

Eden Name	Purpose	Value	Remarks
dataTableSelectable	If set this allows the rows to be selected.	Boolean	This is set up as a property of response.s3
dataTableSelectAll	Only works if dataTableSelectable is set to True. The default is no elements are initially selected, by setting this to True all elements will initially be selected.	Boolean	This is set up as a property of response.s3
dataTableBulkActions	Only works if dataTableSelectable is set to True. This is the label of the button, or a list of labels should you want more than one bulk action.	String or list of Strings	This is set up as a property of response.s3
actions	The list of action buttons to be displayed in the first column. The default action is to send a command to the server a new _jqclick option will attach some java script to the button rather send a request to the server.	List of actions. Each action is a dictionary values. And consists of the label, the HTML class and the URL. Optionally it may also include a restrict list that restricts these buttons to being displayed to just the ids on the list.	This is set up as a property of response.s3
dataTableID auto	The HTML id that will be used to identify this table	String default value 'list'	Set up using s3mgr.configure in the pre-processing
dataTable_iDisplayLength auto	The number of rows to be displayed on a page	Integer	Confusion... dataTable_iDisplayLength and iDisplayLength
no_sspag auto	This will turn off pagination	Boolean	Pagination is the default and nothing needs to be done to enable it.
sortby	This will do an initial sort on the selected column or columns	list of sorting rules. The sorting rules are a list that comprises of the column number followed by the sort direction	default value [[1,'asc']] if this is changed then you also need to use the orderby attribute which is used to order the records coming off from the database.
dataTable_sDom auto	This defines where the controls of the DataTable will be positioned	String explained in the DataTables documentation	default value 'fril<"dataTable_table"t>pi' See ​http://www.datatables.net/usage/options for more information
dataTable_sPaginationType	This defines what controls are displayed to allow the user to page through the records	String either 'two_button' or 'full_numbers' default value 'full_numbers'
dataTableDisplay	This can be used to change how a cell displays the text	List of translations. Each translation is a dictionary item and consists of the column number, the value to look for and its replacement
dataTableResize	If set then the table will resize after download - this allows fields with long body text to have excess hidden behind a button - e.g. comments_represent()	True or False	This is set up as a property of response.s3
dataTableStyleDisabled	This can be used to give a style to represent disabled text	list of ids to apply this style	This adds the class 'disable' to the tr element
dataTableStyleAlert	This can be used to give a style to represent an alert condition for the row	list of ids to apply this style	This adds the class 'alert' to the tr element
dataTableStyleWarning	This can be used to give a style to represent a warning condition for the row	list of ids to apply this style	This adds the class 'warning' to the tr element
hideCol	This can be used to hide the selected columns	list of column ids to be hidden	This will hide the column selected, this column can still be sorted but (obviously) only via code. For Example: response.s3.hideCol = [1]

Examples

The following example illustrate how to implement dataTables in your code. It uses the following base code:

Model code snippet:

  resourcename = "csv"
  tablename = "%s_%s" % (module,
                          resourcename)
  table = db.define_table(tablename,
                          Field("name", required=True, notnull=True),
                          Field("path", type="upload", uploadfield=True, required=True, notnull=True),
                          comments(),
                          *(s3_timestamp() + s3_uid()))

  table.name.comment = DIV(_class = "tooltip",
                           _title = "%s|%s" (T("Name"),
                                              T("Enter a name of the CSV you are uploading.")))

  s3.crud_strings[tablename]= Storage(
          title_create = T("Upload a CSV file"),
          title_list = T("List of CSV files uploaded"),
          label_list_button = T("List of CSV files"),
          )

Controller code snippet:

def csv():

    """ RESTful Controller """

    module = "importer"
    resourcename = "csv"
    tablename = "%s_%s" % (module,
                            resourcename)
    table = db[tablename]

    # Pre-processor
    def prep(r):
        if r.interactive:
            s3mgr.configure(r.tablename,
                            deletable=True,
                            addbtn=True)
        return True

    # Post-processor
    def postp(r, output):
        return output

    response.s3.prep = prep

    response.s3.postp = postp
    return s3_rest_controller(module, resourcename)

This is sufficient to generate a vanilla dataTable as follows:

Adding Buttons

With the default settings two buttons are provided, Open and Delete. Additional buttons can be added by using the actions, or more fully, response.s3.actions. The following code will add an "Import" button.

Add this to the postp() function of the controller:

  def postp(r, output):
      s3_action_buttons(r)
      response.s3.actions = \
      response.s3.actions + [
                              dict(label=str(T("Import")), _class="action-btn",
                                   url=URL(c=module,
                                           f=resourcename,
                                           args=["[id]", "import"])
                                   ),
                            ]
      return output

Notice that before the Open and Delete buttons were provided, now they need to be explicitly added with the call to s3_action_buttons(r), this will also perform the correct permission checks.

Adding Buttons conditionally

Maybe you don't want the ability to import every file. Such as files that are older than a certain date. Also actions can be (and should be) restricted to the users permissions. For more example on how to apply restrictions to a users permissions see the code for s3_action_buttons() in models/00_utils.

Add this to the postp() function of the controller:

  def postp(r, output):
      from datetime import tzinfo, timedelta, datetime
      cutoffDate = datetime.now() - timedelta(days=30)
      query = (r.table.created_on > cutoffDate)
      rows = db(query).select(r.table.id)
      restrict = [str(row.id) for row in rows]
      s3_action_buttons(r)
      response.s3.actions = \
      response.s3.actions + [
                              dict(label=str(T("Import")), _class="action-btn",
                                   url=URL(c=module,
                                           f=resourcename,
                                           args=["[id]", "import"]),
                                   restrict = restrict
                                   ),
                            ]
      return output

The key is the restrict argument that is passed to the Import button. This is set up as a list of record ids that will display the button.

If you want to add additional restrictions to the automatically provided buttons then try the following code, which has been used to restrict the delete button to records with a certain status

  query = (r.table.status == 1) # Status of Pending
  rows = db(query).select(r.table.id)
  try:
      response.s3.actions[1]["restrict"].extend(str(row.id) for row in rows)
  except KeyError: # the restrict key doesn't exist
      response.s3.actions[1]["restrict"] = [str(row.id) for row in rows]
  except IndexError: # the delete buttons doen't exist
      pass 

Adding a javascript button

The standard buttons are basically an HTML A tag which will send a message back to the server. However it is possible to attach some javascript to the button. The following code provides such a button.

Making a action button run some javascript, code taken from s3import._create_import_item_dataTable():

  response.s3.actions = [
                          dict(label= str(self.ImportItemDataTableDisplay),
                               _class="action-btn",
                               _jqclick="$('.importItem.'+id).toggle();",
                              ),
                        ]

This code toggles the show hide class of a table that has been embedded within the dataTable. For this specific instance to be implemented the code that generates the dataTable has been modified. The s3import class has it's own function that builds the dataTable. It is based on the code in s3crud, but is not as general.

Changing the displayed text

Sometimes it can be convenient to change the displayed text of a status field in the dataTable. This can be done by using the response.s3.dataTableDisplay attribute. First I'll change the model to include a status field:

The change to the data model:

  table = db.define_table(tablename,
                          Field("name", required=True, notnull=True),
                          Field("path", type="upload", uploadfield=True, required=True, notnull=True, readable=False),
                          comments(),
                          Field("status", type="integer"),
                          *(s3_timestamp() + s3_uid()))

Now add the code to display a text field depending upon the status:

Add this to the postp() function of the controller:

      values = [dict(col=3, key="1", display=str(T("Uploaded"))),
                dict(col=3, key="2", display=str(T("Imported")))
               ]
      response.s3.dataTableDisplay = values

Sorting by a selected column

To sort a column use the orderby (DAL directive) and sortby (dataTable directive) attributes

Add this to the prep() function of the controller:

          s3mgr.configure(r.tablename,
                          deletable=True,
                          addbtn=True,
                          orderby = tablename+".status",
                          sortby = [[3, 'asc']])            

Selecting Rows

It is possible to select multiple rows by setting the all this requires is to set the dataTableSelectable attribute

Add this to the prep() function of the controller:

      response.s3.dataTableSelectable = True

Highlighting Rows

You can highlight rows. For example set an alert if the file has already been imported or set a warning if the file is more than 60 days old.

Add this to the prep() function of the controller:

      # Display an Alert if the file has already been imported 
      query = (r.table.status == 2)
      rows = db(query).select(r.table.id)
      response.s3.dataTableStyleAlert = [str(row.id) for row in rows]
      # Display a Warning if the file is more than 60 days old 
      warningDate = datetime.now() - timedelta(days=60)
      query = (r.table.created_on < warningDate)
      rows = db(query).select(r.table.id)
      response.s3.dataTableStyleWarning = [str(row.id) for row in rows]

Highlighting Cells

Not Yet Implemented