Editor loads strings as numbers and removes leading zeros

Airprimus · October 2020

Hello,

I have the problem that the editor removes the leading zeros of the article number in a string field when loading the data from the database, so the value can't be written back to the database while editing because the value doesn't exist.

Here is an extract from the database:

s_articles.id | s_articles_supplier.name | s_articles_details.suppliernumber | s_articles_details.ordernumber | s_articles.name                                         | s_articles_details.stockmin | s_articles_details.instock | s_articles_attributes.attr7 | s_articles_attributes.attr6 | s_articles_attributes.lieferant | s_articles_attributes.top_art_lief |
--------------|--------------------------|-----------------------------------|--------------------------------|---------------------------------------------------------|-----------------------------|----------------------------|-----------------------------|-----------------------------|---------------------------------|------------------------------------|
46            | Assa Abloy               | 509X202PZ-----1                   | 0009026                        | AssaAbloy Sicherheitsschloss Motorausführung 509X202PZ | 0                           | -1                         | NULL                        | NULL                        | NULL                            | 0                                  |                           |

And here the data from the JSON:

{"DT_RowId":"row_46","s_articles_supplier":{"name":"Assa Abloy"},"s_articles_details":{"suppliernumber":"509X202PZ-----1","ordernumber":9026,"stockmin":0,"instock":-1},"s_articles":{"name":"AssaAbloy Sicherheitsschloss Motorausf\u00fchrung 509X202PZ"},"s_articles_attributes":{"attr7":null,"attr6":null,"lieferant":null,"top_art_lief":0}}

The controller looks like this:

require "../lib/DataTables.php";

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchPaneOptions; 
    
Editor::inst($db, 's_articles')
    ->field(
        Field::inst('s_articles_supplier.name')
            ->searchPaneOptions(SearchPaneOptions::inst()
                ->value('s_articles_supplier.name')
                ->label('s_articles_supplier.name')
                ->leftJoin('s_articles_supplier', 's_articles.supplierID', '=', 's_articles_supplier.id')
        ),
        Field::inst('s_articles_details.suppliernumber'),
        Field::inst('s_articles_details.ordernumber'),
        Field::inst('s_articles.name'),
        Field::inst('s_articles_details.stockmin')
            ->validator(Validate::numeric()),
        Field::inst('s_articles_details.instock')
            ->validator(Validate::numeric()),
        Field::inst('s_articles_attributes.attr7'),
        Field::inst('s_articles_attributes.attr6'),
        Field::inst('s_articles_attributes.lieferant'),
        Field::inst('s_articles_attributes.top_art_lief')
    )

    ->leftJoin('s_articles_details', 's_articles.id', '=', 's_articles_details.articleID')
    ->leftJoin('s_articles_attributes', 's_articles_details.id', '=', 's_articles_attributes.articledetailsID')
    ->leftJoin('s_articles_supplier', 's_articles.supplierID', '=', 's_articles_supplier.id')

    //->where('s_articles_attributes.top_art_lief', 1)
    ->debug(true)
    ->process($_POST)
    ->json();

and here the js

var editor;
var artikelart = 0; //0 = Alle Artikel, 1 = Top Artikel

$(document).ready(function () {
  function colData(column) {
    var data = [];

    column
      .data()
      .unique()
      .sort()
      .each(function (d, j) {
        data.push({
          id: j,
          text: d,
        });
      });

    return data;
  }

  editor = new $.fn.dataTable.Editor({
    ajax: "./pdo/db_inventory.php",
    table: "#inventory",
    fields: [
      {
        label: "Hersteller:",
        name: "s_articles_supplier.name",
      },
      {
        label: "Lieferant:",
        name: "s_articles_attributes.lieferant",
      },
      {
        label: "Hersteller-Nr:",
        name: "s_articles_details.suppliernumber",
      },
      {
        label: "Shop-Nr:",
        name: "s_articles_details.ordernumber",
      },
      {
        label: "Bezeichnung:",
        name: "s_articles.name",
      },
      {
        label: "Min.:",
        name: "s_articles_details.stockmin",
      },
      {
        label: "Lager:",
        name: "s_articles_details.instock",
        attr: {
          type: "number",
        },
      },
      {
        label: "Regal:",
        name: "s_articles_attributes.attr7",
      },
      {
        label: "Bemerkung:",
        name: "s_articles_attributes.attr6",
      },
    ],
  });

  $("#inventory").on("click", "tbody td.editable", function (e) {
    editor.bubble(
      this,
      [
        "s_articles_details.stockmin",
        "s_articles_details.instock",
        "s_articles_attributes.attr7",
        "s_articles_attributes.attr6",
      ],
      {
        title: "Ihre Eingabe:",
      }
    );
  });

  var table = $("#inventory").DataTable({
    ajax: {
      url: "./pdo/db_inventory.php",
      type: "POST",
    },
    autoWidth: false,
    columns: [
      {
        //Hersteller
        data: "s_articles_supplier.name",
      },
      {
        //Lieferant
        data: "s_articles_attributes.lieferant",
      },
      {
        //Hersteller Nr
        data: "s_articles_details.suppliernumber",
      },
      {
        //Shop Nr
        data: "s_articles_details.ordernumber",
      },
      {
        //Bezeichnung
        data: "s_articles.name",
      },
      {
        //Min
        data: "s_articles_details.stockmin",
        className: "editable",
      },
      {
        //Lager
        data: "s_articles_details.instock",
        className: "editable",
      },
      {
        //Regal
        data: "s_articles_attributes.attr7",
        className: "editable",
      },
      {
        //Bemerkung
        data: "s_articles_attributes.attr6",
        className: "editable",
      },
      {
        //Top Artikel hidden
        data: "s_articles_attributes.top_art_lief",
      },
    ],
    columnDefs: [
      {
        type: "string",
        targets: 3,
        render: function (data, type, row) {
          return (
            '<a href="/backend/?c=article&nr=' +
            data +
            '" target="_black"><span class="btn btn-outline-success btn-sm" role="button" style="width:100%;">' +
            data +
            "</span></a>"
          );
        },
      },
      {
        targets: 6,
        render: function (data, type, row) {
          //Lager < Min
          if (data < row.s_articles_details.stockmin) {
            return (
              '<button type="button" class="btn btn-danger"style="width:100%;"> ' +
              data +
              "</button>"
            );
          }
          //Min = Lager
          if (data === row.s_articles_details.stockmin) {
            return (
              '<button type="button" class="btn btn-warning"style="width:100%;"> ' +
              data +
              "</button>"
            );
          }
          //Lager > Min
          else {
            return (
              '<button type="button" class="btn btn-success"style="width:100%;"> ' +
              data +
              "</button>"
            );
          }
        },
      },
      {
        targets: 9,
        visible: false,
      },
    ],
    searchPanes: {
      layout: "columns-1",
    },
    responsive: true,
    dom:
      '<"dtsp-verticalContainer"<"dtsp-verticalPanes"P><"dtsp-dataTable"Bfrtip>>',
    //dom: "PBfrtip",
    serverSide: true,
    processing: false,
    buttons: [
      {
        text: "Alle Artikel",
        action: function (e, dt, node, config) {
          if (artikelart == 0) {
            this.text("Top Artikel");
            dt.column(9).search("1").draw();
            artikelart = 1;
          } else if (artikelart == 1) {
            this.text("Alle Artikel");
            dt.column(9).search("").draw();
            artikelart = 0;
          }
        },
      },
    ],
    select: true,
    language: {
      oAria: {
        sSortAscending: ": aktivieren, um Spalte aufsteigend zu sortieren",
        sSortDescending: ": aktivieren, um Spalte absteigend zu sortieren",
      },
      select: {
        rows: {
          _: "%d Zeilen ausgewählt",
          0: "",
          1: "1 Zeile ausgewählt",
        },
      },
      buttons: {
        print: "Drucken",
        colvis: "Spalten",
        copy: "Kopieren",
        copyTitle: "In Zwischenablage kopieren",
        copyKeys:
          "Taste <i>ctrl</i> oder <i>\u2318</i> + <i>C</i> um Tabelle<br>in Zwischenspeicher zu kopieren.<br><br>Um abzubrechen die Nachricht anklicken oder Escape drücken.",
        copySuccess: {
          _: "%d Zeilen kopiert",
          1: "1 Zeile kopiert",
        },
        pageLength: {
          "-1": "Zeige alle Zeilen",
          _: "Zeige %d Zeilen",
        },
      },
      searchPanes: {
        title: {
          _: "Filter Aktiv - %d",
          0: "Kein Filter ausgewählt",
          1: "Ein Filter ausgewählt",
        },
        clearMessage: "Zurücksetzen",
        loadMessage: "wird geladen",
        count: "{total} gefunden",
        countFiltered: "{shown} ({total})",
        emptyPanes: "keine Daten gefunden",
      },
    },
  });
});

Is this a bug or do I not see the error?

Thanks Lars

rf1234 · October 2020

You can use a getFormatter for the order number and return something like #0009026 from the server.

Then in JS you can use columns.render to get rid of the #. That probably won't help you in Editor because Editor has no field rendering. So you can either manipulate the value on xhr or you change the editor value on "open" of Editor which is probably easier.

Alternatively you could try forcing to return a string using a getFormatter as well. That might keep the leading zeroes. Maybe worth a try?

Maybe you try the last idea first. If that doesn't work I can help you do the other solutions if you can't get them done.

The last idea is simply this:

Field::inst('s_articles_details.ordernumber')
    ->getFormatter( function ( $val, $data, $opts ) {
        return (string)$val;                  
    } )

json81 · October 2020

Hi!
I have the same problem after updating to Editor: 1.9.5.

Solution with getFormatter didn't help.

Note: $val in "getFormatter" does contain the leading zero but gets removed before it is returned. If adding another char or number (besides 0) it will return the correct number with the leading zero.

/Anders

allan · October 2020

@json81 - That's most curious. I can see it in our examples as well - let me look into it further on Monday and I'll get back to you!

Allan

json81 · December 2020

@allan I have just updated to Editor-1.9.6 and the bug is still present. Did you get a chance to look at it?

Anders

allan · December 2020

Did you update the PHP libraries as well as the Javascript ones? It appears to be working okay in our example now.

Allan

json81 · January 2021

Hi!
Thanks for your hint about the PHP Libraries. I checked them and I used 1.9.5 version. I have now changed to 1.9.6 and it is working.

support armony · February 2022

Hello this solution could save me. How can you check the version of PHP Libraries ?

tangerine · February 2022

Use the Debugger.
https://debug.datatables.net/

support armony · February 2022

Thanks for this tips. It's save me!

Editor loads strings as numbers and removes leading zeros

Editor loads strings as numbers and removes leading zeros

Answers

Howdy, Stranger!

Categories

DataTables

Editor loads strings as numbers and removes leading zeros

Editor loads strings as numbers and removes leading zeros

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables