Editor loads strings as numbers and removes leading zeros

Editor loads strings as numbers and removes leading zeros

AirprimusAirprimus Posts: 8Questions: 3Answers: 0

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

Answers

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257
    edited October 17

    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;                  
        } )
    
Sign In or Register to comment.