| 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: |
| | 53 | The 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: |
| 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" |
| 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 |
| | 110 | Now 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. |
