Filter data from a table through a Dropdown

Question

I have a table returning the amounts paid by taxpayers. However I have to return all the data (because the application adds field to field). So far so good.

But to show the user, I need to filter the table by date (year), for the user to view the annual information.

I made a static example, using Jquery and it’s working the way I need it. The problem is that I’m having difficulties to put in my application’s View, with the data returned from the query.

The working example can be seen here on Jsfiddle.
In the example I use a static select, with the values defined above. However in my View I return the values through a ViewBag.

In the script (filter.js) I’m using, I have to declare the <tr> with the same class I will use in select.

Return of the years: Controller:

ViewBag.AnoExtrato = previdenciaRepository.Previdencias.Where(r => r.CdMatricula == matricula && r.SqContrato == contrato)
            .Select(x => x.dtCompetencia.Value.Year).Distinct().ToList();

And in the view I call select this way:

 <select class="filter" style="width:100px">
        @for (int i = 0; i < ViewBag.AnoExtrato.Count; i++)
        {
            <option value="@ViewBag.AnoExtrato[i]">
                @ViewBag.AnoExtrato[i]
            </option>
        }
    </select>

Follow the rest of the View, showing how the table is populated and performed the sums I need:

<table border="1" id="item-list" >
        <thead>
            <tr>
                <th rowspan="2">
                    Mês
                </th>
                <th rowspan="2">
                    Remuneração<br />
                    Total R$
                </th>
                <th colspan="4">
                    <p align="center">
                        Contribuinte
                    </p>
                </th>
                <th colspan="4">
                    <p align="center">
                        Município
                    </p>
                </th>
            </tr>
            <tr>
                <th>
                    %
                </th>
                <th>
                    Mês R$
                </th>
                <th>
                    Ano R$
                </th>
                <th>
                    Acumulado R$
                </th>
                <th>
                    %
                </th>
                <th>
                    Mês R$
                </th>
                <th>
                    Ano R$
                </th>
                <th>
                    Acumulado R$
                </th>
            </tr>
        </thead>
        <tbody>
            
            @*Variáveis para somar os totais dos campos*@
            @{
                double totalContribuinte = 0;
                double totalMunicipio = 0;
            }

            @foreach (var item in Model.Previdencia.GroupBy(g => new { g.NmPessoa, g.dtCompetencia.Value.Year }))
            {
                @*Variáveis para somar os totais dos campos por ano*@
                double subtotalContribuinte = 0;
                double subtotalMunicipio = 0;

                foreach (var contribuicoes in item.ToList())
                {
                    @*Realizam as somas dos campos*@
                    subtotalContribuinte += contribuicoes.Contribuinte;
                    totalContribuinte += contribuicoes.Contribuinte;

                    subtotalMunicipio += contribuicoes.BaseCalculo;
                    totalMunicipio += contribuicoes.BaseCalculo;


                    <tr>
                        <td>
                            @Html.DisplayFor(modelItem => contribuicoes.dtCompetencia)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => contribuicoes.BaseCalculo)
                        </td>

                        <td>
                            11
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => contribuicoes.Contribuinte)
                        </td>
                        <td>
                            @subtotalContribuinte.ToString("c")
                        </td>
                        <td>
                            @totalContribuinte.ToString("c")
                        </td>

                        <td>
                            11
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => contribuicoes.BaseCalculo)
                        </td>
                        <td>
                            @subtotalMunicipio.ToString("c")
                        </td>
                        <td>
                            @totalMunicipio.ToString("c")
                        </td>
                    </tr>
                }
            }
        </tbody>
    </table>

The JS File I’m using.

$(document).ready( function() {
    
    // any time a filter drop-down changes...
    $("select.filter").change(function () {
        var classes = "";
        var description = "";
    
    // collect the selected filters
    $("select.filter").each(function() {
        if ($(this).val() != '*') {
        classes += "." + $(this).val();
        if (description != '') description += ', ';
        description += $.trim($(this).find("option:selected").text()); 
        }
    });
    
    if (classes == "") {
        // if no filters selected, show all items
        $("table.item-list tr").show();
    } else {
        // otherwise, hide everything...
        $("table.item-list tr").hide();
        // then show only the matching items
        rows = $("table.item-list tr" + classes);
        if (rows.size() > 0) {
        rows.show();
        }
    }
    
    // count up the matching items
    if (description != '') {
        description += " (" + $("table.item-list tr:visible").size() + ")";
    }
    
    // print a description of the active filter
    $("#filter-description").html(description);
    }).change(); // here in case a drop-down has been pre-selected
    
    // just a nice little hover effect
    $("table.item-list tr").hover(
        function() {
            $(this).addClass("hover");
        },
        function() {
            $(this).removeClass("hover");
        }
    );
    });

How do I put the tr of each row equal to the value selected in @Viewbag.Anoextract[i]?

This is the way I found to do it, but if you have a better way to do it, I’m open to suggestions. Remembering that I am not attached to components, can be JS, Jquery, etc.

UPDATE

Just to leave the application running, I added a "Gambiarra" and left the class of <tr> with a list of possible years, thus leaving the Header visible.

 <tr class="1970  1971 1972 1973 1974 1975 1976 1978 1979 1980 1981 1982 1983 1984 1985 1986
                1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002
                2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020">

Answer 1

I’ve put together a very simple example below. Using Partialview, Ajax.Beginform() and a little javascript only.

Controller:

public class FiltroController : Controller
{
  public List<string> Tabela = new List<string>();
  public List<string> Filtro = new List<string>();

  public FiltroController()
  {
     for (int i = 0; i < 10; i++)
     {
        Tabela.Add("Nome " + i.ToString());
     }
     for (int i = 0; i < 10; i++)
     {
        Filtro.Add(i.ToString());
     }
  }
  //
  // GET: /Filtro/

  public ActionResult Index(string filtro)
  {
     ViewBag.Filtro = new SelectList(Filtro);
     if (Request.IsAjaxRequest())
     {
        return PartialView("_Index", Tabela.Where(_ => _.EndsWith(filtro)).ToList());
     }
     return View(Tabela);
  }

}

Index.cshtml

  @model List<string>

  @using (Ajax.BeginForm(null, new AjaxOptions { HttpMethod = "Post", InsertionMode = InsertionMode.Replace, UpdateTargetId = "replaceDiv" }))
  {
     @Html.DropDownList("Filtro", "Selecione")
  }

  <div id="replaceDiv">
     @Html.Partial("_Index", Model)
  </div>

  @section scripts{
     <script>
        $(document).ready(function () {
           $("#Filtro").change(function () {
              $(this).closest("form").submit();
           });
        });
     </script>
  }

And the partialView:

  @model List<string>
  <table class="table table-hover">
     <tbody>

        @foreach (var item in Model)
        {
           <tr>
              <td>@item</td>
           </tr>
        }

     </tbody>
  </table>

Note: I think it’s cool this way for the sake of performace and the fact that it has little javascript, ah and do not forget to add the file

<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>