How to create custom filtering for datetime column prgrammatically?

[Ralf]

Hi,

if have create a class inherits from GridBoundColumn and tryed to add custom controls (2 datetime picker and one reset button).
But I down't know whats wrong, because the filtering does not work.

Can someone explain how to do this right?

Here is the code:

public class DateTimeColumn : GridBoundColumn
    {
        #region Fields

        SPField _field;
        const string _TRACECONST = "ECSpand.SharePoint.UI.WebUserControls.RecordsOrganizer.FileContent.Columns.DateTimeColumn";
        SPDateTimeFieldFormatType _formatType = SPDateTimeFieldFormatType.DateTime;
        private RadDatePicker _datePickerFrom;
        private RadDatePicker _datePickerTo;


        #endregion

        #region Constructor ()

        public DateTimeColumn()
        {

        }

        #endregion

        #region Constructor (field)

        public DateTimeColumn(SPField field)
        {
            this.AllowSorting = true;
            this.DataField = field.Id.ToString();
            this.HeaderText = field.Title;
            this.HeaderTooltip = "";
            _field = field;
            this.SortExpression = field.Id.ToString();
            SPFieldDateTime dtField = field as SPFieldDateTime;
            if (dtField != null)
            {
                _formatType = dtField.DisplayFormat;
            }
        }

        #endregion

        #region Methods

        #region FormatDataValue (dataValue, item)

        protected override string FormatDataValue(object dataValue, GridItem item)
        {
            if (_field == null)
                return dataValue.ToString();

            string s = dataValue as string;
            if (!string.IsNullOrEmpty(s))
            {
                try
                {
                    SPWeb web = SPContext.Current.Web;
                    SPRegionalSettings settings = web.RegionalSettings;
                    if (web.CurrentUser.RegionalSettings != null)
                    {
                        settings = web.CurrentUser.RegionalSettings;
                    }

                    DateTime time;
                    if (s.Contains('T') || s.Contains('Z'))
                    {
                        time = DateTime.Parse(s, CultureInfo.InvariantCulture);
                    }
                    else
                    {
                        time = DateTime.ParseExact(s, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                    }
                    if (_formatType == SPDateTimeFieldFormatType.DateOnly)
                    {
                        return time.ToShortDateString();
                    }
                    else
                    {
                        return Microsoft.SharePoint.Utilities.SPUtility.FormatDate(SPContext.Current.Web, time.ToUniversalTime(), Microsoft.SharePoint.Utilities.SPDateFormat.DateTime);
                    }
                }
                catch (Exception ex)
                {
                    Log.WriteWarning("DateTime-Field '" + _field.InternalName + "' could not be parsed. Value was + '" + dataValue as string + "'. Exception was : " + ex.ToString(), _TRACECONST);
                    return dataValue as string;
                }
            }
            return "-";
        }

        #endregion

        #region  SetupFilterControls (cell)

        /// <summary>
        /// RadGrid will call this method when it initializes the controls inside the filtering item cells
        /// </summary>
        /// <param name="cell">The TableCell</param>
        protected override void SetupFilterControls(System.Web.UI.WebControls.TableCell cell)
        {
            base.SetupFilterControls(cell);
            if (cell.Controls.Count > 0)
                cell.Controls.RemoveAt(0);

            //cell.Style.Add(HtmlTextWriterStyle.WhiteSpace, "nowrap");

            _datePickerFrom = new RadDatePicker();
            //_datePickerFrom.ID = "ColumnFilterDateFrom" + Guid.NewGuid().ToString();
            _datePickerFrom.AutoPostBack = true;
            _datePickerFrom.Skin = "Office2007";
            _datePickerFrom.SelectedDateChanged += new Telerik.Web.UI.Calendar.SelectedDateChangedEventHandler(_datePicker_SelectedDateChanged);
            _datePickerFrom.ShowPopupOnFocus = true;
            _datePickerFrom.Width = Unit.Pixel(100);
            _datePickerFrom.EnableScreenBoundaryDetection = false;
            _datePickerFrom.DatePopupButton.ToolTip = string.Empty;
            _datePickerFrom.Style.Add(HtmlTextWriterStyle.MarginRight, "5px");
            _datePickerFrom.EnableTyping = false;

            _datePickerTo = new RadDatePicker();
            _datePickerTo.ID = "ColumnFilterDateFrom" + Guid.NewGuid().ToString();
            _datePickerTo.AutoPostBack = true;
            _datePickerTo.Skin = "Office2007";
            _datePickerTo.SelectedDateChanged += new Telerik.Web.UI.Calendar.SelectedDateChangedEventHandler(_datePicker_SelectedDateChanged);
            _datePickerTo.ShowPopupOnFocus = true;
            _datePickerTo.Width = Unit.Pixel(100);
            _datePickerTo.EnableScreenBoundaryDetection = false;
            _datePickerTo.DatePopupButton.ToolTip = string.Empty;
            _datePickerTo.Style.Add(HtmlTextWriterStyle.MarginRight, "5px");
            _datePickerTo.EnableTyping = false;

            ImageButton resetfilterbutton = new ImageButton();
            if (!string.IsNullOrEmpty(this.CurrentFilterValue) && this.CurrentFilterFunction != GridKnownFunction.NoFilter)
                resetfilterbutton.ImageUrl = "/_layouts/images/ecspand/filter_remove_green.png";
            else
                resetfilterbutton.Visible = false;
            resetfilterbutton.Width = Unit.Pixel(16);
            resetfilterbutton.Height = Unit.Pixel(16);
            resetfilterbutton.Click += new ImageClickEventHandler(resetfilterbutton_Click);

            cell.Controls.AddAt(0, _datePickerFrom);
            cell.Controls.AddAt(1, _datePickerTo);
            cell.Controls.AddAt(2, resetfilterbutton);
            if (cell.Controls.Count > 3)
                cell.Controls.RemoveAt(3);
        }

        #endregion

        #region _datePicker_SelectedDateChanged (sender, e)

        private void _datePicker_SelectedDateChanged(object sender, Telerik.Web.UI.Calendar.SelectedDateChangedEventArgs e)
        {
            GridFilteringItem filterItem = (GridFilteringItem)((RadDatePicker)sender).NamingContainer;
            try
            {
                if (_datePickerFrom.SelectedDate != null && _datePickerTo.SelectedDate == null)
                    _datePickerTo.SelectedDate = _datePickerFrom.SelectedDate.Value.AddHours(12);

                if (_datePickerFrom.SelectedDate != null && _datePickerTo.SelectedDate != null)
                {
                    filterItem.FireCommandEvent("Filter", new Pair(GridKnownFunction.Between.ToString(), this.UniqueName));
                }
                else
                    filterItem.FireCommandEvent("Filter", new Pair("NoFilter", this.UniqueName));
            }
            catch
            {
            }
        }

        #endregion

        #region resetfilterbutton_Click (sender, e)

        /// <summary>
        /// Handles the Click event of the resetbutton to reset the current column filter.
        /// </summary>
        /// <param name="sender">The sender</param>
        /// <param name="e">The ImageClickEventArgs</param>
        private void resetfilterbutton_Click(object sender, ImageClickEventArgs e)
        {
            if (_datePickerFrom != null)
            {
                this.CurrentFilterValue = string.Empty;
                this.CurrentFilterFunction = GridKnownFunction.NoFilter;
                GridFilteringItem filterItem = (GridFilteringItem)((RadDatePicker)_datePickerFrom).NamingContainer;
                filterItem.FireCommandEvent("Filter", new Pair("NoFilter", this.UniqueName));
            }
        }

        #endregion

        #region  GetCurrentFilterValueFromControl (cell)

        /// <summary>
        /// RadGrid will call this method when the filtering value should be extracted from the filtering input control(s)
        /// </summary>
        /// <param name="cell">The TableCell</param>
        /// <returns>The current filter value</returns>
        protected override string GetCurrentFilterValueFromControl(TableCell cell)
        {
            if (_datePickerFrom.SelectedDate != null)
            {
                string data = string.Format("{0} {1}",  _datePickerFrom.SelectedDate.Value, _datePickerTo.SelectedDate.Value);

                return data;
            }
            else
                return string.Empty;
        }

        #endregion

        #region SetCurrentFilterValueToControl (cell)

        /// <summary>
        /// RadGrid will call this method when the value should be set to the filtering input control(s)
        /// </summary>
        /// <param name="cell">The TableCell</param>
        protected override void SetCurrentFilterValueToControl(TableCell cell)
        {
            if (_datePickerFrom != null)
            {
                base.SetCurrentFilterValueToControl(cell);
                if (!string.IsNullOrEmpty(this.CurrentFilterValue))
                {
                    _datePickerFrom.SelectedDate = this.GetDateTimeValueFormCurrentFilterValue(this.CurrentFilterValue);
                }
            }
        }

        #endregion

        #region GetDateTimeValueFormCurrentFilterValue (value)

        private DateTime? GetDateTimeValueFormCurrentFilterValue(string value)
        {
            DateTime? retval = null;

            if (!string.IsNullOrEmpty(value))
            {
                DateTime tmp = DateTime.MinValue;
                if (DateTime.TryParse(value, out tmp))
                    retval = Convert.ToDateTime(tmp);
            }

            return retval;
        }

        #endregion

        #endregion
    }

Best regards,
Ralf

[Iana Tsolova]

Hi Ralf,

I reviewed your code and if I properly understand you are trying to use the between built-in filter function for filtering this column. If that is true and to make it work, you should make sure the from and the to date are represented by a single string and the only interval in the CurrentFilterValue is this between the dates. If you have more interval, the between filter will not work. Can you check that out and let me know if this is the case?

Greetings,
Iana Tsolova
the Telerik team

[Ralf]

Hello Lana,

yes this exactly what I want. Thank you for information.

I have tryed to combine the two dates in a single string in GetCurrentFilterValueFromControl Method. But the grid will not be filtered. The CurrentFilterFunction was set to Between. I need Between function also for timestamp columns (date including time). When I try to filter a timestamp column by datepart only the EqualTo filter function does not work. So I tryed to use the Between filter function to simulate the EqualTo by using a filtervalue like "25.11.2011 00:00:00 25.11.2011 23:59:59".

When I have selected a date from the picker the SelectedDateChanged event will be fired. The CurrentFilterFunction will be set to Bewteen and the command will be fired. In the GetCurrentFilterValueFromControl the return value will be set as desribed above.
But the grid will not be filtered. Why?

The grid have ro filter its datasource/rows. There is no possibility to to bind the filter to an database, because the application will be used in a SharePoint context. In my page/webpart I only get a datatable from our backend.


Regards,
Ralf

[Iana Tsolova]

Hi Ralf,

When the Between filter function is used, in order to finter its data source, the grid splits the CurretFilterValue by " " (interval) and bilds a filter expression based on the first and the second string after the split. So if you have more than one intervals, it will not work. In this case, instead of using between, you should apply two filters, one requiring GreaterOrEqualTo for the first date, and one applying LessThanOrEqualTo for the second date. If you want to achieve, this with no code, and directly from the UI, you can try enabling the header filter menu. See how it works here.

Kind regards,
Iana Tsolova
the Telerik team

[Ralf]

Hello Iana,

thnak you for information. I will try your suggested solution.

Regards,
Ralf

[Ralf]

Hello Iana,

I hvae tryed to implement, but without success.
I have added the folleowing code:

private void _gridViewContent_ItemCommand(object sender, GridCommandEventArgs e)
        {
            if (e.CommandName == RadGrid.FilterCommandName)
            {
                Pair filterPair = (Pair)e.CommandArgument;
                RadDatePicker picker = (e.Item as GridFilteringItem)[filterPair.Second.ToString()].Controls[0] as RadDatePicker;
                if (picker != null)
                {
                    DateTime? startDate = picker.SelectedDate;
                    if (startDate.HasValue)
                    {
                        _gridViewContent.MasterTableView.FilterExpression = "('" + startDate.Value.ToString() + "' >= [" + filterPair.Second.ToString() + "] AND [" + filterPair.Second.ToString() + "] <= '" + startDate.Value.AddHours(24).AddSeconds(-1).ToString() + "')";
                        //_gridViewContent.Rebind();
                    }
                }

            }
        }

The mastertable filterexpression was set to:
('13.10.2011 00:00:00' >= [MyColumn] AND [MyColumn] <= '13.10.2011 23:59:59')

The column filterfunction was set to NoFilter.
When I debug The master table filterexpression is:
((Convert.ToDateTime(iif(it[MyColumn]==Convert.DBNull,null,it[MyColumn])) >= DateTime.Parse("13.10.2011")) AND ( Convert.ToDateTime(iif(it[MyColumn]==Convert.DBNull,null,it[MyColumn])) <= DateTime.Parse("00:00:00")))

Can you please explain whats the correct way to fset the filter? I prefer a solution in my column class, not in ItemCommand event. Is this possible?

Best regards,
Ralf

[Iana Tsolova]

Hello Ralf,

Try setting the linq expressions for the grid, by setting the EnableLinqExpressions property to false, and see if it makes any difference. When the linq expressions are enabled, you should use Linq syntax to build the filter expressions. To use the SQL syntax for the purpose, you need to disable them.

Best wishes,
Iana Tsolova
the Telerik team