tabalinas / jsgrid-php

Sample project for jsgrid with PHP REST-service
MIT License
91 stars 48 forks source link

Table doesn't show any item, when no filter is selected #22

Closed dombre77 closed 7 years ago

dombre77 commented 7 years ago

Hello and thanks for this nice script, I´ve got some issues here using your jsgrid-php-script. The json output of my /items/ doesn´t work when I select no filter option - I expect an output of all db-entries. First of all I have no clients and countries but items and places :-). I think the problem is my getAll-Method, because it seems that the filter is used anytime although I do not choose a filter.

Part of ItemRepository.php ` public function getAll($filter) { $item_id = "%" . $filter["item_id"] . "%"; $item_active = "%" . $filter["item_active"] . "%"; $item_place = $filter["item_place"]; $item_sort = $filter["item_sort"]; $item_type = $filter["item_type"]; $item_description = "%" . $filter["item_description"] . "%"; $item_adddesc = "%" . $filter["item_adddesc"] . "%"; $item_amount = "%" . $filter["item_amount"] . "%"; $item_pallets = "%" . $filter["item_pallets"] . "%"; $item_sheetperpallet = "%" . $filter["item_sheetperpallet"] . "%"; $item_width = "%" . $filter["item_width"] . "%"; $item_height = "%" . $filter["item_height"] . "%"; $item_weight = "%" . $filter["item_weight"] . "%"; $item_run = $filter["item_run"]; $item_time = "%" . $filter["item_time"] . "%"; $item_aunumber = "%" . $filter["item_aunumber"] . "%"; $item_arrival = "%" . $filter["item_arrival"] . "%"; $item_machine = $filter["item_machine"]; $item_keyaccount = $filter["item_keyaccount"]; $item_parents = "%" . $filter["item_parents"] . "%";

    $sql = "SELECT * FROM items WHERE
            item_id LIKE :item_id AND
            item_active LIKE :item_active AND
            (item_place = 0 OR item_place = :item_place) AND
            (item_sort = 0 OR item_sort = :item_sort) AND
            (item_type = 0 OR item_type = :item_type) AND
            item_description LIKE :item_description AND
            item_adddesc LIKE :item_adddesc AND
            item_amount LIKE :item_amount AND
            item_pallets LIKE :item_pallets AND
            item_sheetperpallet LIKE :item_sheetperpallet AND
            item_width LIKE :item_width AND
            item_height LIKE :item_height AND
            item_weight LIKE :item_weight AND
            (item_run = 0 OR item_run = :item_run) AND
            item_time LIKE :item_time AND
            item_aunumber LIKE :item_aunumber AND
            item_arrival LIKE :item_arrival AND
            (item_machine = 0 OR item_machine = :item_machine) AND
            (item_keyaccount = 0 OR item_keyaccount = :item_keyaccount) AND
            item_parents LIKE :item_parents;";

    $q = $this->db->prepare($sql);
    $q->bindParam(":item_id", $item_id);
    $q->bindParam(":item_active", $item_active);
    $q->bindParam(":item_place", $item_place);
    $q->bindParam(":item_sort", $item_sort);
    $q->bindParam(":item_type", $item_type);
    $q->bindParam(":item_description", $item_description);
    $q->bindParam(":item_adddesc", $item_adddesc);
    $q->bindParam(":item_amount", $item_amount);
    $q->bindParam(":item_pallets", $item_pallets);
    $q->bindParam(":item_sheetperpallet", $item_sheetperpallet);
    $q->bindParam(":item_width", $item_width);
    $q->bindParam(":item_height", $item_height);
    $q->bindParam(":item_weight", $item_weight);
    $q->bindParam(":item_run", $item_run);
    $q->bindParam(":item_time", $item_time);
    $q->bindParam(":item_aunumber", $item_aunumber);
    $q->bindParam(":item_arrival", $item_arrival);
    $q->bindParam(":item_machine", $item_machine);
    $q->bindParam(":item_keyaccount", $item_keyaccount);
    $q->bindParam(":item_parents", $item_parents);

    $q->execute();
    $rows = $q->fetchAll();

    $result = array();
    foreach ($rows as $row) {
        array_push($result, $this->read($row));
    }
    return $result;
}`

This is the part of my items/index.php:

switch ($_SERVER["REQUEST_METHOD"]) { case "GET": $result = $items->getAll(array( "item_id" => $_GET["item_id"], "item_active" => $_GET["item_active"], "item_place" => intval($_GET["item_place"]), "item_sort" => intval($_GET["item_sort"]), "item_type" => intval($_GET["item_type"]), "item_description" => $_GET["item_description"], "item_adddesc" => $_GET["item_adddesc"], "item_amount" => $_GET["item_amount"], "item_pallets" => $_GET["item_pallets"], "item_sheetperpallet" => $_GET["item_sheetperpallet"], "item_width" => $_GET["item_width"], "item_height" => $_GET["item_height"], "item_weight" => $_GET["item_weight"], "item_run" => intval($_GET["item_run"]), "item_time" => $_GET["item_time"], "item_aunumber" => $_GET["item_aunumber"], "item_arrival" => $_GET["item_arrival"], "item_machine" => intval($_GET["item_machine"]), "item_keyaccount" => intval($_GET["item_keyaccount"]), "item_parents" => $_GET["item_parents"] )); break; [...]

The Request URL shows up like this: Request URL: http://dev.test.default/items/?item_parents=&item_place_id=0&item_sort_id=0&item_type_id=0&item_description=&item_adddesc=&item_run=0&item_keyaccount=0&item_aunumber=&item_arrival=&item_machine_id=0&item_time=

With a JSON result of [] :-(

I tried several manipulations, but I do not find the mistake...Here´s my JS as well:

`$(function () {

$.ajax({
    type: "GET",
    url: "/places/"
}).done(function (places) {

    places.unshift({place_id: "0", place_code: "bitte wählen..."});

    var sorts = [
        { sort_name: "bitte wählen...", sort_id: 0 },
        { sort_name: "Bilderdruck", sort_id: 1 },
        { sort_name: "Offset", sort_id: 2 },
        { sort_name: "Karton", sort_id: 3 },
        { sort_name: "Sonstiges", sort_id: 4 },
    ];

    var types = [
        { type_name: "bitte wählen...", type_id: 0 },
        { type_name: "Glanz", type_id: 1 },
        { type_name: "Matt", type_id: 2 },
        { type_name: "Offset", type_id: 3 },
        { type_name: "Sonstiges", type_id: 4 }
    ];

    var runs = [
        { run_name: "bitte wählen...", run_id: 0 },
        { run_name: "SB", run_id: 1 },
        { run_name: "BB", run_id: 2 }
    ];

    var keyaccounts = [
        { key_name: "bitte wählen...", key_id: 0 },
        Some names shortened for privacy....
        { key_name: "Bubi", key_id: 10 }
    ];

    var machines = [
        { machine_name: "bitte wählen...", machine_id: 0 },
        { machine_name: "KBA 105", machine_id: 1 },
        { machine_name: "KBA 106", machine_id: 2 },
        { machine_name: "Bubi", machine_id: 3 },
        { machine_name: "Sonstige", machine_id: 4 }
    ];

    $("#jsGrid").jsGrid({
        height: "100%",
        width: "100%",
        filtering: true,
        inserting: true,
        editing: true,
        sorting: true,
        paging: true,
        autoload: true,
        pageSize: 10,
        pageButtonCount: 5,
        deleteConfirm: "Do you really want to delete item?",
        controller: {
            loadData: function (filter) {
                return $.ajax({
                    type: "GET",
                    url: "/items/",
                    data: filter
                });
            },
            insertItem: function (item) {
                return $.ajax({
                    type: "POST",
                    url: "/items/",
                    data: item
                });
            },
            updateItem: function (item) {
                return $.ajax({
                    type: "PUT",
                    url: "/items/",
                    data: item
                });
            },
            deleteItem: function (item) {
                return $.ajax({
                    type: "DELETE",
                    url: "/items/",
                    data: item
                });
            }
        },
        fields: [
            {name: "item_id", type: "number", title: "ID", width: 80},
            {name: "item_parents", type: "text", title: "Eltern", width: 80},
            {name: "item_active", type: "checkbox", title: "Aktiv", sorting: false, filtering: false, width: 50},
            {
                name: "item_place_id",
                type: "select",
                items: places,
                valueField: "place_id",
                textField: "place_code",
                title: "Stellplatz",
                width: 80
            },
            {
                name: "item_sort_id",
                type: "select",
                items: sorts,
                valueField: "sort_id",
                textField: "sort_name",
                title: "Sorte",
                width: 80
            },
            {
                name: "item_type_id",
                type: "select",
                items: types,
                valueField: "type_id",
                textField: "type_name",
                title: "Typ",
                width: 80
            },
            {name: "item_description", type: "text", title: "Beschrb"},
            {name: "item_adddesc", type: "text", title: "Zusatz"},
            {name: "item_amount", type: "number", title: "Menge"},
            {name: "item_width", type: "number", title: "Breite"},
            {name: "item_height", type: "number", title: "Höhe"},
            {name: "item_weight", type: "number", title: "Gewicht"},
            {name: "item_run", type: "select", items: runs, valueField: "run_id", textField: "run_name", title: "Bahn", width: 80},
            {name: "item_keyaccount", type: "select", items: keyaccounts, valueField: "key_id", textField: "key_name", title: "Vertrieb"},
            {name: "item_aunumber", type: "text", title: "AU-Nr.", width: 80},
            {name: "item_arrival", type: "text", title: "Termin", width: 80},
            {name: "item_machine_id", type: "select", items: machines, valueField: "machine_id", textField: "machine_name", title: "Maschine"},
            {name: "item_pallets", type: "number", title: "Paletten", width: 80},
            {name: "item_sheetperpallet", type: "number", title: "Bogen/Palette", width: 80},
            {name: "item_time", type: "text", title: "Zeit", width: 80},
            {type: "control"}
        ]
    });
});

});`

It would be nice, if you could help me in some way. THx Dominik

dombre77 commented 7 years ago

I solved that problem by adding ":" to the following statement:

(item_place = 0 OR item_place = :item_place) AND ... should be

(:item_place = 0 OR item_place = :item_place) AND

Regards, Dominik