1. Home
  2. Forum
  3. JavaScript - EJ 2
  4. Display dropdown in date option, with rolling time periods.

Display dropdown in date option, with rolling time periods.

So basically my idea is that I would like to display in the query builder, on date options, drop down options for last 6 months, last year last week, etc. I would like to put these in the operators, with options for within last month, which would just output a hard coded [ value > DATEADD(m, -1, GETDATE()) AND value <= GETDATE() ] in the sql output, however I cannot see anywhere that custom operators are allowed, if this is possible please tell me. In the mean time I have been pursuing the option of a template. Using the documentation as a guide I made this:

                newcol.template = {

                    create: function () {

                        var elem = document.createElement('input');

                        elem.setAttribute('type', 'text');

                        return elem;

                    },

                    destroy: function (args) {

                        var element = document.getElementById(args.elementId);

                        for (var i = 0; i < element.ej2_instances.length; i++) {

                            if (element.ej2_instances[i].dropdownverification != null) {

                                element.ej2_instances[i].destroy();

                            }

                        }

                    },

                    write: function (args) {

                        //var ds = ['Cash', 'Debit Card', 'Credit Card', 'Net Banking', 'Wallet'];

                        var dateOptions = [

                            { text: 'Custom', value: 'Custom' },

                            { text: 'Last 24 hours', value: 'DATEADD(hh, -24, GETDATE())' },

                            { text: 'Last week', value: 'DATEADD(d, -7, GETDATE())' },

                            { text: 'Last month', value: 'DATEADD(m, -1, GETDATE())' },

                            { text: 'Last six months', value: 'DATEADD(m, -6, GETDATE())' },

                            { text: 'Last twelve months', value: 'DATEADD(m, -12, GETDATE())' }

                        ]

 

                        var newdropDownObj = new ej.dropdowns.DropDownList({

                            dataSource: dateOptions,

                            text: args.values ? args.values : dateOptions[1].text,

                            fields: { value: 'value', text: 'text' },

                            valueTemplate: '<div id="inputfield-' + args.elements.id + '">${text}</div>',

                            change: function (e) {

                                if (e.itemData.value == 'Custom') {

                                    console.log('boops');

                                }

 

                                qryBldrObj.notifyChange(e.itemData.value, e.element);

                            }

                        });

                        newdropDownObj.dropdownverification = 'true';

                        newdropDownObj.appendTo('#' + args.elements.id);

                    }

                };  



Now the plan was to append a datetime picker to the template of the dropdownmenu when the custom option was selected, however I haven't gotten that far as the value being output to the query builder has qoutes around it when exported to sql. Not sure why, also any change to the dropdown menu does not propegate up using the notifyChange.
If there is no option for a custom operator, help with this would be wonderful.


5 Replies
GK Gayathri KarunaiAnandam Syncfusion Team March 26, 2021 07:46 AM UTC

Hi David, 

Thank you for contacting Syncfusion Support. 

We have checked your reported query. We have achieved your requirement by using RuleTemplate in Querybuilder. We have render the DateRangePicker Component in Range field in which the label and the range are customized.  Please check the below code snippet. 

var filter = [ 
  { field: "Name", label: "Name", type: "string" }, 
  { field: "Range", label: "Range", type: "date", ruleTemplate: "#rangeTemplate" } 
]; 
var qryBldrObj = new ej.querybuilder.QueryBuilder({ 
  columns: filter, 
  width: "100%", 
  actionBegin: actionBegin 
}); 

function actionBegin(args) { 
  ruleID = args.ruleID; 
  args.rule.operator = 'between'; 
  if (args.requestType === "template-create") { 
    fieldObj = new ej.dropdowns.DropDownList({ 
      dataSource: this.columns, 
      fields: args.fields, 
      value: args.rule.field, 
      change: function (e) { 
        qryBldrObj.notifyChange(e.value, e.element, "field"); 
      } 
    }); 

    valueObj = new ej.calendars.DateRangePicker({ 
  presets: [ 
    { 
      label: "This Week", 
      start: new Date( 
        new Date( 
          new Date().setDate( 
            new Date().getDate() - ((new Date().getDay() + 7) % 7) 
          ) 
        ).toDateString() 
      ), 
      end: new Date( 
        new Date( 
          new Date().setDate( 
            new Date( 
              new Date().setDate( 
                new Date().getDate() - ((new Date().getDay() + 7) % 7) 
              ) + 6 
            ).getDate() + 6 
          ) 
        ).toDateString() 
      ) 
    }, 
    { 
      label: "Last Week", 
      start: new Date( 
        new Date(new Date().setDate(new Date().getDate() - 6)).toDateString() 
      ), 
      end: new Date(new Date().toDateString()) 
    }, 
    { 
      label: "This Month", 
      start: new Date(new Date(new Date().setDate(1)).toDateString()), 
      end: new Date(new Date().toDateString()) 
    }, 
    { 
      label: "Last Month", 
      start: new Date( 
        new Date( 
          new Date(new Date().setMonth(new Date().getMonth() - 1)).setDate(1) 
        ).toDateString() 
      ), 
      end: new Date(new Date().toDateString()) 
    } 
  ], 
  format: "dd'/'MMMM'/'yyyy", 
  change: function (e) { 
        qryBldrObj.notifyChange(e.value, e.element, "value"); 
      } 
}); 
    fieldObj.appendTo("#" + args.ruleID + "_filterkey"); 
    valueObj.appendTo("#" + args.ruleID + "_valuekey0"); 
  } 
} 

 
For your reference, we have prepared a sample based on your requirement. Please check the below link. 


 
Please get back to us, if you need further assistance. 

Regards, 
Gayathri K 


DH David House March 26, 2021 05:25 PM UTC

Ok! so this is pretty close to what I'm looking for, but instead of actually selecting the date when you hit last six months, I need to output the sql code for selecting that date, as these options are being used to make stored widgets which have to always have current data (last six months rolling), and this sql code is called each time. Where as what you've got a date picker that simply selects that date, but it is super close. Also what I've got working at the moment is to create columns with a template if it's a date option, with new operators, which have json for their values, then I pick it apart at the export stage and edit it as needed. It works but the delete button on the option is in the wrong location and it seems like a bandaid fix. Any help you could give me with an option where it outputs sql that is using getdate(), like: [ BETWEEN DATEADD(d, -7 GETDATE()) AND GETDATE() ]  would be much appreciated.

GK Gayathri KarunaiAnandam Syncfusion Team March 29, 2021 03:01 PM UTC

Hi David, 

We have checked your reported query. We can add custom date in DateRangePicker using change event. We have prepared a sample based on your scenario as demonstrated in the below code snippet. 

valueObj = new ej.calendars.DateRangePicker({ 
      change: function(e) { 
        switch (e.daySpan) { 
          case 2: 
            e.value = ["DATEADD(hh, -24, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
 
          case 7: 
            e.value = ["DATEADD(d, -7, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          case 28: 
            e.value = ["DATEADD(m, -1, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          case 180: 
            e.value = ["DATEADD(m, -6, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
 
          case 363: 
            e.value = ["DATEADD(m, -12, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          default: 
            /* Optional */ 
            e.value = "Custom"; 
        } 
        qryBldrObj.notifyChange(e.value, e.element, "value"); 
      } 
    }); 
    fieldObj.appendTo("#" + args.ruleID + "_filterkey"); 
    valueObj.appendTo("#" + args.ruleID + "_valuekey0"); 
  } 
 
  
Please check the below sample. 


Please get back to us, if you need further assistance. 

Regards, 
Gayathri K 


DH David House replied to Gayathri KarunaiAnandam March 29, 2021 04:24 PM UTC

Hi David, 

We have checked your reported query. We can add custom date in DateRangePicker using change event. We have prepared a sample based on your scenario as demonstrated in the below code snippet. 

valueObj = new ej.calendars.DateRangePicker({ 
      change: function(e) { 
        switch (e.daySpan) { 
          case 2: 
            e.value = ["DATEADD(hh, -24, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
 
          case 7: 
            e.value = ["DATEADD(d, -7, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          case 28: 
            e.value = ["DATEADD(m, -1, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          case 180: 
            e.value = ["DATEADD(m, -6, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
 
          case 363: 
            e.value = ["DATEADD(m, -12, GETDATE())", "GETDATE()"]; 
            break; /* optional */ 
          default: 
            /* Optional */ 
            e.value = "Custom"; 
        } 
        qryBldrObj.notifyChange(e.value, e.element, "value"); 
      } 
    }); 
    fieldObj.appendTo("#" + args.ruleID + "_filterkey"); 
    valueObj.appendTo("#" + args.ruleID + "_valuekey0"); 
  } 
 
  
Please check the below sample. 


Please get back to us, if you need further assistance. 

Regards, 
Gayathri K 


So, several issues, one you cannot save to json and load again, that's a big one for me, as when you do so it just saves out the actual value instead of the outputed value from the on change event, but even more pressing is the issue of the output this creates to the sql is not formatted to be taken by sql:

DeliveryDate BETWEEN 'DATEADD(m, -12, GETDATE())' AND 'GETDATE()'"

Notice the 'qoutes' around the different parts, this is getting thrown back by entity framework.

SP Sangeetha Priya Murugan Syncfusion Team April 6, 2021 11:14 AM UTC

 
Hi David, 
 
Thank you for your update. 
 
We have checked your reported issue and we have logged this as defect and the fix will be available in our April 14th weekly patch release, we appreciate your patience until then. You can track the status of this defects using below link from our feedback portal,  
 
 
Regards, 
Sangeetha M 


Loader.
Up arrow icon