[Solved] Filter column on 2 dates (between) at server side

[Joseph RAMBLAS]

Hello,

I've got a grid with date columns and I'd like to filter these columns selecting 2 dates (start and end) and apply "between" filter.
My grid is built dynamically at server side, as I don't know in advance what kind of columns it will contain.
So, to allow filtering, I've created a filter template with 2 RadDatePicker.
But I don't know how I can use FireCommandEvent to make filter work.
If I select one date, and I use GreaterThan or LessThan, it works fine, but how to invoke FireCommandEvent to take the 2 dates in input, and apply "Between" filter ?

Tanks for your answers

Here is my code :

The template filter for date is implemented by the CustomFilterForDateGridColumn class.

Here is the aspx code for the control containing the grid :

<%@ Control AutoEventWireup="false" CodeBehind="wucCompteHistoPrets.ascx.vb" Inherits="OPAC_LOCAL.wucCompteHistoPrets" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<asp:Panel ID="PanelHistoPrets" runat="server">
    <asp:label id="MSG_ERREUR" runat="server" CssClass="MessageErreur" Visible="False"></asp:label>
    <telerik:RadGrid id="GrilleHistoPrets" runat="server" EnableEmbeddedSkins="false" Width="100%" Skin="Default"
        PageSize="20" PagerStyle-AlwaysVisible="true" AllowPaging="true" AutoGenerateColumns="false"
        GroupingEnabled="false" ShowGroupPanel="false" AllowMultiRowSelection="true" AllowSorting="true"
        ClientSettings-AllowDragToGroup="false" ClientSettings-Selecting-AllowRowSelect="true"
        SortingSettings-SortToolTip="Cliquer pour trier la colonne" SortingSettings-SortedDescToolTip="Tri ascendant" SortingSettings-SortedAscToolTip="Tri descendant"
        OnNeedDataSource="GrilleHistoPrets_NeedDataSource" OnItemDataBound="GrilleHistoPrets_ItemDataBound"
        OnItemCreated="GrilleHistoPrets_ItemCreated"
        OnItemCommand="GrilleHistoPrets_ItemCommand"
        ShowStatusBar="true"
        >
        <MasterTableView PageSize="20" runat="server"
            AllowAutomaticDeletes="false" AllowAutomaticInserts="false" AllowAutomaticUpdates="false"
            NoMasterRecordsText="Aucun prêt" NoDetailRecordsText="Aucun prêt"
            ShowHeadersWhenNoRecords="true"
            CommandItemDisplay="Top"
            >
            <Columns>
            </Columns>
            <CommandItemTemplate>
                <div align="right" style="padding:4px 0px 0px 6px;">
                    <asp:ImageButton runat="server" ID="btnClearFilters" CommandName="ClearFilters" />
                    <asp:Button runat="server" ID="btnTexteClearFilters" CommandName="ClearFilters" />
                </div>
            </CommandItemTemplate>
        </MasterTableView>
        <PagerStyle AlwaysVisible="true" Mode="NextPrevAndNumeric"
            FirstPageText="" FirstPageToolTip="Aller à la première page"
            NextPageText="" NextPageToolTip="Aller à la page suivante"
            LastPageText="" LastPageToolTip="Aller à la dernière page"
            PrevPageText="" PrevPageToolTip="Aller à la page précédente"
            NextPagesToolTip="Afficher les pages suivantes" PrevPagesToolTip="Afficher les pages précédentes"
            PagerTextFormat="{4}{5} prêt(s) sur {1} page(s)"
        />
    </telerik:RadGrid>
</asp:Panel>

And the code behind : the method which build the grid dynamically.

    Protected Sub AfficherGrillePrets()
        Dim i As Integer = 0
        Dim strTxt As String = ""
        SessionsConfig.GetConfTextes("TexteAucunPret", strTxt)
        GrilleHistoPrets.MasterTableView.NoDetailRecordsText = strTxt
        GrilleHistoPrets.MasterTableView.NoMasterRecordsText = strTxt
        GrilleHistoPrets.MasterTableView.Columns.Clear()
        GrilleHistoPrets.AllowFilteringByColumn = _AutoriserFiltrage

        If Not IsNothing(ListeColonneHistoPrets) AndAlso ListeColonneHistoPrets.Count > 0 Then
            Dim colID As GridBoundColumn

            colID = New GridBoundColumn
            colID.DataField = "Id"
            colID.Visible = False
            GrilleHistoPrets.MasterTableView.Columns.Add(colID)

            Dim myEnum As IEnumerator = ListeColonneHistoPrets.GetEnumerator()
            Dim sLibCol As String
            Dim sTypeCol As String = "T"
            i = 1
            While myEnum.MoveNext
                sLibCol = CStr(myEnum.Current)
                sTypeCol = "T"
                If CStr(myEnum.Current).Contains("|") Then
                    Try
                        sLibCol = CStr(myEnum.Current).Split("|")(0)
                        sTypeCol = CStr(myEnum.Current).Split("|")(1)
                    Catch ex As Exception
                    End Try
                End If
                Select Case sTypeCol
                    Case "T"
                        Dim col As New GridBoundColumn
                        col.DataField = "val" & i
                        col.HeaderText = sLibCol
                        col.ItemStyle.HorizontalAlign = HorizontalAlign.Left
                        col.AllowFiltering = False
                        GrilleHistoPrets.MasterTableView.Columns.Add(col)
                    Case "D"
                        Dim col As New GridDateTimeColumn
                        col.DataField = "valD" & i
                        col.DataType = GetType(System.DateTime)
                        col.DataFormatString = "{0:dd/MM/yyyy}"
                        col.HeaderText = sLibCol
                        col.AllowFiltering = False
                        col.ItemStyle.HorizontalAlign = HorizontalAlign.Left
                        GrilleHistoPrets.MasterTableView.Columns.Add(col)
                    Case "TF"
                        Dim col As New CustomFilterForTextGridColumn(ListeLigneHistoPrets, "val" & i)
                        col.DataField = "val" & i
                        col.HeaderText = sLibCol
                        col.AllowFiltering = _AutoriserFiltrage
                        col.ItemStyle.HorizontalAlign = HorizontalAlign.Left
                        GrilleHistoPrets.MasterTableView.Columns.Add(col)
                    Case "DF"
                        Dim col As New CustomFilterForDateGridColumn(ListeLigneHistoPrets, "valD" & i)
                        col.DataField = "valD" & i
                        col.DataType = GetType(System.DateTime)
                        col.DataFormatString = "{0:dd/MM/yyyy}"
                        col.HeaderText = sLibCol
                        col.AllowFiltering = _AutoriserFiltrage
                        col.ItemStyle.HorizontalAlign = HorizontalAlign.Left
                        GrilleHistoPrets.MasterTableView.Columns.Add(col)
                End Select
                i += 1
            End While
        End If
    End Sub

The CustomFilterForDateGridColumn class :

Imports Telerik.Web.UI

Public Class CustomFilterForDateGridColumn
    Inherits GridBoundColumn

    Private _DataTable As List(Of PODCompteEntite)
    Private _DataField As String
    Private Property _DateDebut() As String
        Get
            Return CStr(System.Web.HttpContext.Current.Session("DateDebut" & Me.UniqueName))
        End Get
        Set(ByVal value As String)
            System.Web.HttpContext.Current.Session("DateDebut" & Me.UniqueName) = value
        End Set
    End Property
    Private Property _DateFin() As String
        Get
            Return CStr(System.Web.HttpContext.Current.Session("DateFin" & Me.UniqueName))
        End Get
        Set(ByVal value As String)
            System.Web.HttpContext.Current.Session("DateFin" & Me.UniqueName) = value
        End Set
    End Property

    Public Sub New(ByVal pDataTable As List(Of PODCompteEntite), ByVal pDataField As String)
        _DataTable = pDataTable
        _DataField = pDataField
    End Sub

    Protected Overrides Sub SetupFilterControls(ByVal cell As System.Web.UI.WebControls.TableCell)
        MyBase.SetupFilterControls(cell)
        cell.Controls.RemoveAt(0)
        Dim dateDebut As New RadDatePicker()
        dateDebut.ID = "dateDebut" & Me.UniqueName
        dateDebut.Width = Unit.Pixel(100)
        dateDebut.AutoPostBack = False
        Dim dateFin As New RadDatePicker()
        dateFin.ID = "dateFin" & Me.UniqueName
        dateFin.Width = Unit.Pixel(100)
        dateFin.AutoPostBack = False
        Dim sChargement As String = ""
        SessionsConfig.GetConfTextes("TexteChargementEnCours", sChargement)
        cell.Controls.AddAt(0, New LiteralControl("<div align=""right"">De "))
        cell.Controls.AddAt(1, dateDebut)
        cell.Controls.AddAt(2, New LiteralControl("</div>"))
        cell.Controls.AddAt(3, New LiteralControl("<div align=""right"">A "))
        cell.Controls.AddAt(4, dateFin)
        cell.Controls.AddAt(5, New LiteralControl("</div>"))
        cell.Controls.AddAt(6, New LiteralControl("<div align=""right"">"))
        Dim btnFiltrer As New ImageButton
        btnFiltrer.ID = "btnFiltrer" & Me.UniqueName
        cell.Controls.AddAt(7, btnFiltrer)
         cell.Controls.AddAt(8, New LiteralControl("</div>"))
        cell.Controls.RemoveAt(9)
    End Sub

    Protected Overrides Sub SetCurrentFilterValueToControl(ByVal cell As System.Web.UI.WebControls.TableCell)
        MyBase.SetCurrentFilterValueToControl(cell)
        Dim dateDebut As RadDatePicker = CType(cell.Controls(1), RadDatePicker)
        If _DateDebut <> String.Empty Then
            dateDebut.SelectedDate = Date.Parse(_DateDebut)
        End If
        Dim dateFin As RadDatePicker = CType(cell.Controls(4), RadDatePicker)
        If _DateFin <> String.Empty Then
            dateFin.SelectedDate = Date.Parse(_DateFin)
        End If
    End Sub

    Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As System.Web.UI.WebControls.TableCell) As String
        Dim sDate As String = String.Empty
        _DateDebut = String.Empty
        _DateFin = String.Empty
        Dim dateDebut As RadDatePicker = CType(cell.Controls(1), RadDatePicker)
        Dim dateFin As RadDatePicker = CType(cell.Controls(4), RadDatePicker)
        If Not IsNothing(dateDebut.SelectedDate) Then
            sDate = dateDebut.SelectedDate.ToString()
            _DateDebut = dateDebut.SelectedDate.ToString()
        End If
        If Not IsNothing(dateFin.SelectedDate) Then
            _DateFin = dateFin.SelectedDate.ToString()
            sDate = dateFin.SelectedDate.ToString()
        End If
        Return sDate
    End Function

    Private Sub BoutonFiltrer_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
        Dim filterItem As GridFilteringItem = CType(CType(sender, ImageButton).NamingContainer, GridFilteringItem)
        filterItem.FireCommandEvent("Filter", New Pair("GreaterThan", Me.UniqueName))
    End Sub
End Class

[Schlurk]

If you read through this forum thread and look at the samples posted I think you'll get a good idea of how to do the "between" filter.