Skip to content

Grid With Date Filter

1 Purpose

This template example can be used for general lists or reports. It uses sc-datatables and the html panel components to create a reporting grid, along with a styling definition for the panel. This template contains a filter function that is controlled by a ruleset which are used to filter the template on one of the date fields.

2 Template Example

   "components": [
        {
            "componentName": "sc-static-html",
            "fullWidth": true,
            "name": "panelStyle",
            "html": "<style>.panel-A {background:#f9f9f9;border:0px;margin-top:-25px} .panel-A > .panel-heading {font-weight:normal;font-size:1.25em;background:#1D1B31;color:white} .btnC {background:#1D1B31;color:white}</style>"
        },
        {
            "componentName": "sc-static-html",
            "fullWidth": true,
            "wrapAction": "open",
            "wrapHtmlType": "wrap",
            "wrapClass": "panel-A",
            "name": "panel1",
            "label": "Journey Events"
        },
        {
            "componentName": "sc-drop-down",
            "label": "Pre-Set Times",
            "name": "preSetTimes",
            "disableSave": true,
            "dropDownList": [
                "Yesterday",
                "Last Week",
                "Last Month",
                "Last Calendar Year",
                "Last 7 Days",
                "Month To Date",
                "Current Financial Year To Date",
                "Previous Financial Year",
                "Last (rolling) 12 months"
            ]
        },
        {
            "componentName": "sc-date-time",
            "name": "startDateFilter",
            "label": "Started",
            "disableSave": true
        },
        {
            "componentName": "sc-date-time",
            "name": "endDateFilter",
            "label": "Ended",
            "disableSave": true
        },
        {
            "componentName": "sc-button",
            "caption": "Filter",
            "style": "btn btn-primary btn-sm",
            "name": "filterButton"
        },
        {
            "componentName": "sc-button",
            "name": "clearFilterButton",
            "caption": "Clear",
            "style": "btn btn-primary btn-sm"
        },
        {
            "componentName": "sc-datatables",
            "name": "reportGrid",
            "detail": "",
            "columnSearch": true,
            "disableSave": true,
            "delayBeforeCallNum": 200,
            "showReload": true,
            "filter": "{'query':{'bool':{'filter':[{'term':{'systemHeader.systemType':'document'}},{'term':{'systemHeader.templateId':'fd138380-69e2-11e8-ba94-5709595b6655'}}]}}}",
            "fullWidth": true,
            "gridColumns": [
                {
                    "displayName": "Name",
                    "field": "systemHeader.summaryName",
                    "width": 4,
                    "enableColumnResizing": true,
                    "enableColumnMoving": true,
                    "href": "/#/form/{{{documentId}}}",
                    "type": "url",
                    "urlOpenIn": "newWindow",
                    "sort": {
                        "precedence": 1,
                        "direction": "asc"
                    }
                },
                {
                    "displayName": "Started",
                    "field": "startDate",
                    "width": 1,
                    "enableColumnResizing": true,
                    "enableColumnMoving": true,
                    "type": "date",
                    "filter": "dateFilter",
                    "sort": {
                        "precedence": 2,
                        "direction": "asc"
                    }
                },
                {
                    "displayName": "Ended",
                    "field": "endDate",
                    "width": 1,
                    "enableColumnResizing": true,
                    "enableColumnMoving": true,
                    "type": "date",
                    "filter": "dateFilter"
                },
                {
                    "displayName": "Journey Type",
                    "field": "imeiDocumentType",
                    "width": 1,
                    "enableColumnResizing": true,
                    "enableColumnMoving": true,
                    "type": "string"
                }
            ],
            "listAllDocuments": false,
            "mandatory": false,
            "label": " "
        },
        {
            "componentName": "sc-static-html",
            "name": "panel1",
            "label": "",
            "wrapHtmlType": "wrap",
            "wrapAction": "close"
        }
    ],
    "hideCommandBar": true,
    "appTags": [
        "test",
        "testSub"
    ],

3 Ruleset

The ruleset assumes that the 'JayRule' include is available. The 'use' comment block shows details on how to modify for a template

// RuleSet: RAM - Grid Filter - OnFieldChange
// Updated by: paul.arkell@fieldtec.com 2018-06-17 10:41:21 +10:00
{
#include "JayRule Ruleset Overlay JS",

    ruleset : {

        name : 'Datatables Date Filter Function - OnFieldChange',

        /* use:
        in the ruleGetQry block....
        case '@templateId which contains grid' :
            ntf.qryObj = {'eqry' : '@query from grid' , gridName : '@name field of grid' , 'startDateName' : '@name of date field to filter on' };
        break;
        */

        ruleGetQry : {
            ruleCondition : function(ntf) {
                return true;
            },

            ruleAction : function(ntf) {

                var tpId = ntf.context.templateId;
                ntf.getQry = true;

                switch (tpId){
                    case 'c498c160-69e6-11e8-9689-4181aea1edc8' : // Journey Events Report
                        ntf.qryObj = {'eqry' : {'query':{'bool':{'filter':[{'term':{'systemHeader.templateId':'fd138380-69e2-11e8-ba94-5709595b6655'}},{'range':{'startDate':{'gte':'now-14d'}}} ]}}} , 'gridName' : 'reportGrid' ,'startDateName' : 'startDate'};
                    break;

                    case '014e21b0-6f4e-11e8-95ee-810dc53a7295' : // Journeys Without Daily Driver Report
                        ntf.qryObj = {'eqry': {'query':{'bool':{'filter':[{'term':{'flagChecksheetAbsenceNotified':true}},{'term':{'systemHeader.systemType':'document'}}]}}}, 'gridName':'reportGrid' ,'startDateName' : 'startDate'};
                    break;

                    default: ntf.getQry = false;
                };
            }
        },

        ruleFilterGrid : {
            ruleCondition : function(ntf) {
                var fieldChanged = ntf.context.fieldChanged;
                return (fieldChanged === 'filterButton' && 
                ntf.getQry);
            },

            ruleAction : function(ntf) {

                var ft3 = ntf.scope;
                var endDate = ft3.moment(ntf.document.endDateFilter);
                var startDate = ft3.moment(ntf.document.startDateFilter);

                if(endDate.isBefore(startDate)){
                    ntf.errorMessage = 'End Date Must Be > Start Date';
                } else {
                    var startDate = ntf.qryObj.startDateName;
                    ntf.qryObj.eqry.query.bool.filter.push({'range':{ startDate:{'gte':ntf.document.startDateFilter, 'lte':ntf.document.endDateFilter }}} );

                    var eqry = ntf.qryObj.eqry;
                    var gridName = ntf.qryObj.gridName;
                    ft3.setFilter(''+gridName+'',eqry);
                }
            }
        },

        ruleFilterClear : {
            ruleCondition : function(ntf) {
                var fieldChanged = ntf.context.fieldChanged;
                return (fieldChanged === 'clearFilterButton'
                && ntf.getQry);
            },

            ruleAction : function(ntf) {

                var ft3 = ntf.scope;
                delete ntf.document.endDateFilter;
                delete ntf.document.startDateFilter;
                delete ntf.document.preSetTimes;

                var eqry = ntf.qryObj.eqry;
                var gridName = ntf.qryObj.gridName;
                ft3.setFilter(''+gridName+'',eqry);

            }
        },

        rulePreSetDates : {
            ruleCondition : function(ntf) {
                var fieldChanged = ntf.context.fieldChanged;
                return (fieldChanged === 'preSetTimes');
            },

            ruleAction : function(ntf) {

                var timeSelect = ntf.document.preSetTimes;
                var ft3 = ntf.scope;
                switch(timeSelect){
                    case 'Yesterday' : 
                        ntf.document.startDateFilter = ft3.moment().startOf('day').subtract(1,'days');
                        ntf.document.endDateFilter = ft3.moment().startOf('day').subtract(1,'second');
                    break;

                    case 'Last Week' :
                        ntf.document.startDateFilter = ft3.moment().startOf('week').subtract(1,'weeks');
                        ntf.document.endDateFilter = ft3.moment().startOf('week').subtract(1,'second');
                    break;

                    case 'Last Month' :
                        ntf.document.startDateFilter = ft3.moment().startOf('month').subtract(1,'months');
                        ntf.document.endDateFilter = ft3.moment().startOf('month').subtract(1,'second');
                    break;

                    case 'Last Calendar Year' :
                        ntf.document.startDateFilter = ft3.moment().startOf('year').subtract(1,'years');
                        ntf.document.endDateFilter = ft3.moment().startOf('year').subtract(1,'second');
                    break;

                    case 'Last 7 Days' :
                        ntf.document.startDateFilter = ft3.moment().startOf('day').subtract(1,'weeks');
                        ntf.document.endDateFilter = ft3.moment();
                    break;

                    case 'Month To Date' :
                        ntf.document.startDateFilter = ft3.moment().startOf('month');
                        ntf.document.endDateFilter = ft3.moment();
                    break;

                    case 'Current Financial Year To Date' :
                        if(ft3.moment().month() >=6 ){
                            ntf.document.startDateFilter = ft3.moment().startOf('year').add(6,'months');
                            ntf.document.endDateFilter = ft3.moment();
                        } else {
                            ntf.document.startDateFilter = ft3.moment().startOf('year').subtract(6,'months');
                            ntf.document.endDateFilter = ft3.moment();
                        }
                    break;

                    case 'Previous Financial Year' :
                        if(ft3.moment().month() >=6 ){
                            ntf.document.startDateFilter = ft3.moment().startOf('year').subtract(1,'year').add(6,'months');
                            ntf.document.endDateFilter = ft3.moment().startOf('year').add(6,'months').subtract(1,'second');
                        } else {
                            ntf.document.startDateFilter = ft3.moment().startOf('year').subtract(1,'year').subtract(6,'months');
                            ntf.document.endDateFilter = ft3.moment().startOf('year').subtract(6,'months').subtract(1,'second');
                        }
                    break;

                    case 'Last (rolling) 12 months' :
                        ntf.document.startDateFilter = ft3.moment().subtract(1,'year');
                        ntf.document.endDateFilter = ft3.moment();
                    break;

                default :
                    delete ntf.document.startDateFilter;
                    delete ntf.document.endDateFilter;
                }
            }
        }
    }
}