Changes between Version 22 and Version 23 of DeveloperGuidelines/DataTables


Ignore:
Timestamp:
09/04/12 11:16:41 (9 years ago)
Author:
graeme
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • DeveloperGuidelines/DataTables

    v22 v23  
    1010{{{
    1111#!div style="font-size: 80%"
    12 Model code snippet:
     12Controller code snippet:
    1313  {{{#!python
    1414        from s3.s3utils import S3DataTable
     
    4949   }}}
    5050}}}
     51This code gets the rows from the resource and passes the data into the S3DataTable html() function the extra parameter of note is the dt_pagination which when set to 'false' will turn server side pagination off. All the data will be sent to the client and !DataTables will manage the filtering a sorting itself.
    5152
    52 To add a table the controller will need first initialise the table and then manage requests from !DataTables for more data.
    53 
    54 == Properties ==
    55 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:
    56 ||= Eden Name =||= Purpose =||= Value =||= Remarks =||
    57 || dataTableSelectable || If set this allows the rows to be selected. || Boolean || This is set up as a property of response.s3 ||
    58 || 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 ||
    59 || 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 ||
    60 || 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 ||
    61 || 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 ||
    62 || dataTable_iDisplayLength \\'''auto''' || The number of rows to be displayed on a page || Integer || ''Confusion...'' dataTable_iDisplayLength and iDisplayLength ||
    63 || no_sspag \\'''auto''' || This will turn off pagination || Boolean || Pagination is the default and nothing needs to be done to enable it. ||
    64 || 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.||
    65 || 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 ||
    66 || 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' || ||
    67 || 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 || ||
    68 || 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 ||
    69 || 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 ||
    70 || 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 ||
    71 || 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 ||
    72 || 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]}}} ||
    73 
    74 == Examples ==
    75 The following example illustrate how to implement dataTables in your code. It uses the following base code:
     53The next stage is to build a controller that can manage pagination. To do this the controller will need to additionally manage requests from !DataTables for more data.  This can be set up as follows:
    7654{{{
    7755#!div style="font-size: 80%"
    78 Model code snippet:
    79   {{{#!python
    80     resourcename = "csv"
    81     tablename = "%s_%s" % (module,
    82                             resourcename)
    83     table = db.define_table(tablename,
    84                             Field("name", required=True, notnull=True),
    85                             Field("path", type="upload", uploadfield=True, required=True, notnull=True),
    86                             comments(),
    87                             *(s3_timestamp() + s3_uid()))
    88 
    89     table.name.comment = DIV(_class = "tooltip",
    90                              _title = "%s|%s" (T("Name"),
    91                                                 T("Enter a name of the CSV you are uploading.")))
    92 
    93     s3.crud_strings[tablename]= Storage(
    94             title_create = T("Upload a CSV file"),
    95             title_list = T("List of CSV files uploaded"),
    96             label_list_button = T("List of CSV files"),
    97             )
    98   }}}
    9956Controller code snippet:
    10057  {{{#!python
    101 def csv():
     58        from s3.s3utils import S3DataTable
     59        vars = request.get_vars
     60        resource = s3db.resource("inv_warehouse")
     61        # When a request is sent from DataTables it sends various information
     62        # about what data the user has requested, this includes details about
     63        # records the user wants and any sort or the filter instructions
    10264
    103     """ RESTful Controller """
    104 
    105     module = "importer"
    106     resourcename = "csv"
    107     tablename = "%s_%s" % (module,
    108                             resourcename)
    109     table = db[tablename]
    110 
    111     # Pre-processor
    112     def prep(r):
    113         if r.interactive:
    114             s3mgr.configure(r.tablename,
    115                             deletable=True,
    116                             addbtn=True)
    117         return True
    118 
    119     # Post-processor
    120     def postp(r, output):
    121         return output
    122 
    123     response.s3.prep = prep
    124 
    125     response.s3.postp = postp
    126     return s3_rest_controller(module, resourcename)
     65        # Get the start and end details from dataTables
     66        start = int(vars.iDisplayStart) if vars.iDisplayStart else 0
     67        limit = int(vars.iDisplayLength) if vars.iDisplayLength else s3mgr.ROWSPERPAGE
     68        totalrows = resource.count()
     69        list_fields = ["id",
     70                       "name",
     71                       "organisation_id",
     72                       ]
     73        # Get the filter and sort instructions from dataTables
     74        rfields = resource.resolve_selectors(list_fields)[0]
     75        (orderby, filter) = S3DataTable.getControlData(rfields, current.request.vars)
     76        # Now set up the resource filter and find out how many row are in the filtered resource
     77        resource.add_filter(filter)
     78        filteredrows = resource.count()
     79        # Get all the data from the resource
     80        rows = resource.select(list_fields,
     81                               orderby="organisation_id",
     82                               start=start,
     83                               limit=limit,
     84                               )
     85        if rows:
     86            data = resource.extract(rows,
     87                                    list_fields,
     88                                    represent=True,
     89                                    )
     90            dt = S3DataTable(rfields, data)
     91            dt.defaultActionButtons(resource)
     92            if request.extension == "html":
     93                # Get the html for the initial call to the dataTable
     94                warehouses = dt.html(totalrows,
     95                                     filteredrows,
     96                                     "warehouse_list",
     97                                     )
     98            else:
     99                # Get any subsequent request for data which will be sent back as json
     100                warehouse = dt.json("warehouse_list",
     101                                    int(vars.sEcho),
     102                                    totalrows,
     103                                    filteredrows,
     104                                    )
     105                return warehouse
     106        else:
     107            warehouses = "No warehouses exist"
    127108   }}}
    128109}}}
    129 This is sufficient to generate a vanilla dataTable as follows:
    130 [[BR]]
    131 [[Image(DataTable_Plain.png)]]
    132 [[BR]]
    133 === Adding Buttons ===
    134 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.
    135 {{{
    136 #!div style="font-size: 80%"
    137 Add this to the postp() function of the controller:
    138   {{{#!python
    139     def postp(r, output):
    140         s3_action_buttons(r)
    141         response.s3.actions = \
    142         response.s3.actions + [
    143                                 dict(label=str(T("Import")), _class="action-btn",
    144                                      url=URL(c=module,
    145                                              f=resourcename,
    146                                              args=["[id]", "import"])
    147                                      ),
    148                               ]
    149         return output
    150    }}}
    151 }}}
    152 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.
    153 [[BR]]
    154 [[Image(DataTable_Plus_Import_Btn.png)]]
    155 [[BR]]
    156 === Adding Buttons conditionally ===
    157 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.
    158 {{{
    159 #!div style="font-size: 80%"
    160 Add this to the postp() function of the controller:
    161   {{{#!python
    162     def postp(r, output):
    163         from datetime import tzinfo, timedelta, datetime
    164         cutoffDate = datetime.now() - timedelta(days=30)
    165         query = (r.table.created_on > cutoffDate)
    166         rows = db(query).select(r.table.id)
    167         restrict = [str(row.id) for row in rows]
    168         s3_action_buttons(r)
    169         response.s3.actions = \
    170         response.s3.actions + [
    171                                 dict(label=str(T("Import")), _class="action-btn",
    172                                      url=URL(c=module,
    173                                              f=resourcename,
    174                                              args=["[id]", "import"]),
    175                                      restrict = restrict
    176                                      ),
    177                               ]
    178         return output
    179    }}}
    180 }}}
    181 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.
    182 [[BR]]
    183 [[Image(DataTable_Plus_Cond_Import_Btn.png)]]
    184 [[BR]]
    185 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
    186 {{{
    187 #!div style="font-size: 80%"
    188   {{{#!python
    189     query = (r.table.status == 1) # Status of Pending
    190     rows = db(query).select(r.table.id)
    191     try:
    192         response.s3.actions[1]["restrict"].extend(str(row.id) for row in rows)
    193     except KeyError: # the restrict key doesn't exist
    194         response.s3.actions[1]["restrict"] = [str(row.id) for row in rows]
    195     except IndexError: # the delete buttons doen't exist
    196         pass
    197   }}}
    198 }}}
    199 === Adding a javascript button ===
    200 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.
    201 {{{
    202 #!div style="font-size: 80%"
    203 Making a action button run some javascript, code taken from s3import._create_import_item_dataTable():
    204   {{{#!python
    205     response.s3.actions = [
    206                             dict(label= str(self.ImportItemDataTableDisplay),
    207                                  _class="action-btn",
    208                                  _jqclick="$('.importItem.'+id).toggle();",
    209                                 ),
    210                           ]
    211   }}}
    212 }}}
    213 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.
    214 
    215 === Changing the displayed text ===
    216 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:
    217 {{{
    218 #!div style="font-size: 80%"
    219 The change to the data model:
    220   {{{#!python
    221     table = db.define_table(tablename,
    222                             Field("name", required=True, notnull=True),
    223                             Field("path", type="upload", uploadfield=True, required=True, notnull=True, readable=False),
    224                             comments(),
    225                             Field("status", type="integer"),
    226                             *(s3_timestamp() + s3_uid()))
    227   }}}
    228 }}}
    229 [[Image(DataTable_with_Status.png)]]
    230 [[BR]]
    231 Now add the code to display a text field depending upon the status:
    232 {{{
    233 #!div style="font-size: 80%"
    234 Add this to the postp() function of the controller:
    235   {{{#!python
    236         values = [dict(col=3, key="1", display=str(T("Uploaded"))),
    237                   dict(col=3, key="2", display=str(T("Imported")))
    238                  ]
    239         response.s3.dataTableDisplay = values
    240   }}}
    241 }}}
    242 [[Image(DataTable_with_Status_Labels.png)]]
    243 [[BR]]
    244 === Sorting by a selected column ===
    245 To sort a column use the orderby (DAL directive) and sortby (dataTable directive) attributes
    246 {{{
    247 #!div style="font-size: 80%"
    248 Add this to the prep() function of the controller:
    249   {{{#!python
    250             s3mgr.configure(r.tablename,
    251                             deletable=True,
    252                             addbtn=True,
    253                             orderby = tablename+".status",
    254                             sortby = [[3, 'asc']])           
    255   }}}
    256 }}}
    257 [[Image(DataTable_Sorted by_Status.png)]]
    258 [[BR]]
    259 === Selecting Rows ===
    260 It is possible to select multiple rows by setting the all this requires is to set the dataTableSelectable attribute
    261 {{{
    262 #!div style="font-size: 80%"
    263 Add this to the prep() function of the controller:
    264   {{{#!python
    265         response.s3.dataTableSelectable = True
    266   }}}
    267 }}}
    268 [[Image(DataTable_with_Selected_Rows.png)]]
    269 [[BR]]
    270 === Highlighting Rows ===
    271 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.
    272 {{{
    273 #!div style="font-size: 80%"
    274 Add this to the prep() function of the controller:
    275   {{{#!python
    276         # Display an Alert if the file has already been imported
    277         query = (r.table.status == 2)
    278         rows = db(query).select(r.table.id)
    279         response.s3.dataTableStyleAlert = [str(row.id) for row in rows]
    280         # Display a Warning if the file is more than 60 days old
    281         warningDate = datetime.now() - timedelta(days=60)
    282         query = (r.table.created_on < warningDate)
    283         rows = db(query).select(r.table.id)
    284         response.s3.dataTableStyleWarning = [str(row.id) for row in rows]
    285   }}}
    286 }}}
    287 [[Image(DataTable_with_Highlighting.png)]]
    288 [[BR]]
    289 === Highlighting Cells ===
    290 Not Yet Implemented
     110Now the controller needs to manage the two standard request extensions for !dataTables: html which is used for the initial table; and !aaData which is used for subsequent calls. When the data is prepared for the !dataTable it needs to know the total rows in the table and the number of rows that are available after filtering, the filter information is returned from !dataTables and the static helper method S3DataTable.getControlData will return the filter and sort information which the user has set up. The json method requires an extra parameter the sEcho which it passes to the server itself. This value just needs to be returned back to the client. '''Note''' that the html call no longer requires the dt_pagination parameter.