CRUD with jQuery, PHP and SQL Server: table with total, subtotal and auto filter

Asked

Viewed 991 times

0

Hello, Community!

I have tested some jQuery plugins to generate table that support CRUD. Here’s my review of them.

  1. jQuery jTable

Excellent plugin, but has annoying limitations like horizontal scrolling, which does not exist. If you have a table with many fields, the div where the table is created adds a scrolling-x, but the table header and footer scroll together with the body. It has an auto filter plugin, but not with select boxes. The grid is generated through JSON. This is great, because the addition and inclusion form fields on the server use the returned fields to generate each input, select or textarea. As it uses jQuery UI, we only need to pass the data types of each field and the formatting is automatic. It is not compatible with Bootstrap. Some points of conflict are modals and forms, which is serious. It is possible to handle actions independently. For example, I needed to include all fields in a BD, but I could only change some once included. The plugin allows you to do this and does not disable fields that cannot be edited, preserving them in the include form.

  1. jqGrid

It has great potential, but the documentation is horrible, very bad. It is compatible with Bootstrap and jQuery UI (native). It allows actions to be handled independently as well, but it is a bit more complicated, because of the positioning of each item (first has to be the update, then the Insert, then the delete, etc., and then you change the order by carelessness),s and at least the documentation helped. You can do everything perfectly, but you don’t have the auto filter. On the server side, all you have to do is generate the JSON and you’re done. Pagination and sorting of results the client side takes care. I liked it a lot, but since it’s not intuitive and the documentation doesn’t help, it’s almost impossible to treat CRUD actions separately. Everything involves functions, so your code becomes giant for a table... I mean, too big for anything, if you need something complete.

  1. Datatables

Very good too, but everything is server side practically and it is complicated to treat Insert and update separately. It has an auto filter mode, but it surrounds the server side. I don’t like that. The codes to generate the full table are large and each step involves a query practically, so it is not scalable. It has a lot of server side work, but the result is good. Compatible with Bootstrap and jQuery UI.

  1. Bootstrap Table

Excellent plugin too. I think it should be the best to some extent. It allows inline editing and treats Insert and update separately, but that’s because you don’t have a "native" Insert form, you have to generate it yourself. This is the only problem: the data inclusion form. Besides, everything on the client side, even the export pro Excel can be done on the client side, PDF and image as well. You can make an auto filter by manually including the fields Forms that will be searched in the table, but the search is on the client side. Almost perfect!

NOW I CRY

I need a plugin that manages the table in an easy way and the forms as well, but that allows the independent treatment of update and Insert. I NEED to include auto filters and subtotal and sum. Does anyone know if this miracle exists? Or how I can use these plugins to generate a full CRUD, but with the specifications I passed?

jquery jqgrid
  • 1

    Eduardo, your question is very open. But let me give you a hint: look at the grids that you most like - a matter of taste - and work to make this grid do what it needs. And when you have a difficulty on time, put here that we help you. PS: I like the Datatables, very flexible, level, has much feature only client-side, auto-filter, paging, editing within the grid, etc.

  • How can I restrict this question? It is that Datatables, despite having a lot of resources, everything that is done by the user has to be treated on the server side. Or am I wrong in this? And I sincerely hope it’s wrong, because I need these resources and apparently I haven’t found them. Thank you, sir!!!

  • 1

    On the website of examples from Datatable all examples are client-side, except for the server-side category.

1 answer

0


Hello, Community!

I decided to use Datatables and have everything I asked in the original question: autofiltro (with select or input), you can sort, search, export to PDF or Excel, copy, print, have an editor (which is not free, have to pay for the license)... well, it is quite complete!

Here is an example code:

$('#tabela').DataTable({
        scrollX: true,
        dom: "Bfrtip",
        idSrc: 'id',
        ajax: "json.php",
        columns: [
            //tem que se o mesmo nome das chaves do arquivo json retornado
            {data: "campo1"},
            {data: "campo2"},
            {data: "campo3"},
            {data: "campo4"},
            {data: "campo5"},
            {data: "campo6"}
        ],
        select: true,
        //botoes
        buttons: [
            {extend: "create", editor: editor,
                formButtons: [
                    'Salvar',
                    {label: 'Cancelar', fn: function () {
                            this.close();
                        }
                    }
                ]
            },
            {extend: "edit", editor: editor,
                formButtons: [
                    'Salvar',
                    {label: 'Cancelar', fn: function () {
                            this.close();
                        }
                    }
                ]
            },
            {extend: "remove", editor: editor,
                formButtons: [
                    'Salvar',
                    {label: 'Cancelar', fn: function () {
                            this.close();
                        }
                    }
                ]
            },
            {extend: "print", text: "Imprimir"},
            {extend: "copy", text: "Copiar"},
            {extend: "excel"}
        ],
        // codigo do auto filtro
        initComplete: function (row, data, start, end, display) {
            var api = this.api(), data;
            api.columns().every(function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                        .appendTo($(column.footer()).empty())
                        .on('change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                    $(this).val()
                                    );

                            column
                                    .search(val ? '^' + val + '$' : '', true, false)
                                    .draw();
                        });

                column.data().unique().sort().each(function (d, j) {
                    select.append('<option value="' + d + '">' + d + '</option>');
                });
            });
        },
        // codigo do total e do subtotal
        drawCallback: function (row, data, start, end, display) {
            var api = this.api(), data;
            // remove os formatos pra receber as strings como float
            var intVal = function (i) {
                return typeof i === 'string' ?
                        i.replace(/[\$,.]/g, '') * 1 :
                        typeof i === 'number' ?
                        i : 0;
            };
            // total de financiamento (geral, filtros aplicados)
            total = api
                    .column(10, {filter: "applied"})
                    .data()
                    .reduce(function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0);

            // total por pagina
            pageTotal = api
                    .column(10, {page: 'current'})
                    .data()
                    .reduce(function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0);

            $('#total').html(
                '<b>Subtotal da página atual:</b> R$ ' + $.number( pageTotal/100, 2, ',', '.' ) + ' <br> <b> Valor total:</b> R$ ' + $.number( total/100, 2, ',', '.' )
            );
        },
        //i18
        language: {
            processing: "Processando...",
            search: "Pesquisa:",
            lengthMenu: "Afficher _MENU_ &eacute;l&eacute;ments",
            info: "Mostrando _START_ a _END_ de _TOTAL_ registros",
            infoEmpty: "Mostrando 0 a 0 de 0 registros",
            infoFiltered: "(de _MAX_ registros no total)",
            infoPostFix: "",
            loadingRecords: "Carregando...",
            select: {
                rows: {
                    _: "%d linhas selecionadas",
                    1: "1 linha selecionada"
                }
            },
            zeroRecords: "Nenhum registro encontrado",
            emptyTable: "Não há registros a serem exibidos",
            paginate: {
                first: "Primeiro",
                previous: "Anterior",
                next: "Próximo",
                last: "Último"
            },
            aria: {
                sortAscending: ": activer pour trier la colonne par ordre croissant",
                sortDescending: ": activer pour trier la colonne par ordre décroissant"
            }
        }
    });
<!-- estilos -->

<link href="assets/plugins/DataTables/DataTables-1.10.11/css/dataTables.bootstrap.css" rel="stylesheet">
        <link href="assets/plugins/script/DataTables/Responsive-2.0.2/css/responsive.bootstrap.min.css" rel="stylesheet">
        <link href="assets/plugins/script/DataTables/Buttons-1.1.2/css/buttons.bootstrap.min.css" rel="stylesheet">
        <link href="assets/plugins/script/DataTables/Select-1.1.2/css/select.bootstrap.min.css" rel="stylesheet">
        

<!-- scripts -->
<script src="assets/plugins/slimscroll/jquery.slimscroll.min.js"></script>
        <script src="assets/plugins/jquery-cookie/jquery.cookie.js"></script>
        <script src="assets/plugins/DataTables/DataTables-1.10.11/js/jquery.dataTables.min.js"></script>
        <script src="assets/plugins/DataTables/DataTables-1.10.11/js/dataTables.bootstrap.min.js"></script>
        <script src="assets/plugins/DataTables/Responsive-2.0.2/js/dataTables.responsive.js"></script>
        <script src="assets/plugins/DataTables/Buttons-1.1.2/js/dataTables.buttons.min.js"></script>
        <script src="assets/plugins/DataTables/Buttons-1.1.2/js/buttons.bootstrap.min.js"></script>
        <script src="assets/plugins/DataTables/Select-1.1.2/js/dataTables.select.min.js"></script>
        <script src="assets/plugins/DataTables/Buttons-1.1.2/js/dataTables.buttons.min.js"></script>
        <script src="assets/plugins/DataTables/Buttons-1.1.2/js/buttons.print.min.js"></script>
        <script src="assets/plugins/DataTables/Buttons-1.1.2/js/buttons.html5.min.js"></script>
        <script src="assets/plugins/jszip/jszip.min.js"></script>

<table id="tabela" class="table table-striped table-bordered table-responsive table-condensed responsive nowrap" cellspacing="0" width="100%">
                                    <thead>
                                        <tr>
                                            <!-- mesmo numero de colunas do arquivo json -->
                                            <th class="all">Campo1</th>
                                            <th class="all">Campo2</th>
                                            <th class="all">Campo3</th>
                                            <th class="all">Campo4</th>
                                            <th class="all">Campo5</th>
                                            <th class="all">campo6</th>
                                        </tr>
                                    </thead>
                                    <tfoot>
                                        <tr>
                                            <!-- onde sera mostrado o auto filtro -->
                                            <th class="all">Campo1</th>
                                            <th class="all">Campo2</th>
                                            <th class="all">Campo3</th>
                                            <th class="all">Campo4</th>
                                            <th class="all">Campo5</th>
                                            <th class="all">campo6</th>
                                        </tr>
                                    </tfoot>
                                </table>

Browser other questions tagged jquery jqgrid

You are not signed in. Login or sign up in order to post.