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

0

Hello, Community!

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

  • 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 scrolling-x, but the table header and footer roll together with the body. It has an auto filter plugin, but not with select boxes. The grid is generated using JSON. This is great because the fields in the form of adding and adding data to the server use the fields returned to generate each input, select, or textarea. As you use 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 the manners and forms, which is serious. You can treat actions independently. For example, I needed to include all fields in a DB, but I could only change a few once included. The plugin allows you to do this and does not disable the fields that can not be edited, preserving them in the inclusion form.

  • jqGrid
  • It has great potential, but the documentation is awful, really 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 it has to be the update, then the insert, then the delete, etc, and then you change the order by carelessness), if at least the documentation helped. You can do everything perfectly, but you do not have the auto filter. On the server side, you just need to generate the same JSON and that's it. Paging and ordering results the client side takes care of. I liked it a lot, but since it is not intuitive and the documentation does not help, it was almost impossible to treat CRUD actions separately. Everything involves functions, so your code gets huge for a table ... I mean, too big for anything, if you need something complete.

  • 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 does involve the server side. I did not like that. The codes for generating the complete table are large, and each step involves a query pretty much, so it gets scalable. It has a lot of server side work, but the result is good. Compatible with Bootstrap and jQuery UI.

  • BootStrap Table
  • Excellent plugin too. I think it should be the best to some extent. It allows inline editing and treats the insert and update separately, but this does not have a "native" insert form, you have to generate it in the same hand. That's the only problem: the form of data inclusion. Otherwise, everything from the client side to the export to Excel can be done from the client side, PDF and image as well. You can do a self filter manually including the forms of the fields that will be searched in the table, but the search is on the client side. Almost perfect!

    NOW I CHORUS

    I need a plugin that manages the table in an easy way and forms as well, but which allows for independent update and insert handling. I need to include auto filters and subtotal and sum. Does anyone know if this miracle exists? Or how can I use these plugins to generate a full CRUD, but with the specs I've passed?

        
    asked by anonymous 03.12.2015 / 19:30

    1 answer

    0

    Hello, Community!

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

    Here is a sample 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>
        
    20.05.2016 / 04:41