Get logChange values to variables

Helpdesk Lionesa

Hello,

I would like to get the value of the logs to show in the table,

Example of log:
{"gestcol_colaboradores":{"id_colaborador":"1231233","nome":"Nome LastName","data_nasc":"2022-09-01","nif":"22222222","sexo":"1","nacionalidade":"11","telefone":"999999999","email":"email@email.com","morada":"MORADATESTE","cp":"9999-999","localidade":"Local","distrito":"2","dc":"1","data_atendimento":"0000-00-00","notas":""}}

My JS

/*
 * Editor client script for DB table colaboradores_logs
 * Created by http://editor.datatables.net/generator
 */

(function($){

  function format ( d ) {
      // `d` is the original data object for the row
      return '<table cellpadding="" cellspacing="" border="0" style="width:100%;table-layout: fixed;" >'+
          '<tr>'+
              '<th style="width:100%">Dados:</th>'+
          '</tr>'+
          '<tr>'+
              '<td>'+d.valores+'</td>'+
      '</table>';
  }

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/logs/table.logs_colaboradores.php',
        table: '#logs_gestcol_colaboradores',
        fields: [

            {
                "label": "Utilizador:",
                "name": "utilizador"
            },
            {
                "label": "Atividade:",
                "name": "atividade"
            },
            {
                "label": "Dados Colaborador:",
                "name": "valores",
                "type": "textarea"
            },
            {
                "label": "ID Colaborador:",
                "name": "id_colaborador"
            },
            {
                "label": "Data:",
                "name": "data"
            }
        ]
    } );

    var table = $('#logs_gestcol_colaboradores').DataTable( {
    order: [[ 5, "desc" ],],
        scrollCollapse: true,
        pageLength : 30,
        language: {"url": "js/Portuguese.json"},
        dom: 'Bfrtip',
        ajax: 'php/logs/table.logs_colaboradores.php',
        columns: [
      {
                "className":      'dt-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
      },
            {
                "data": "utilizador"
            },
            {
                "data": "atividade"
            },
            {
                "data": "valores", "visible": false, //log values
            },
            {
                "data": "id_colaborador"
            },
            {
                "data": "data"
            }
        ],

    rowCallback: function ( row, data, index ) {
                        if ( data.atividade == "create" ) {
                        $('td', row).css('background-color', 'rgb(0, 255, 0, 0.2)');
                        }
                        else if ( data.atividade == "edit" ) {
                        $('td', row).css('background-color', 'rgb(255, 255, 0, 0.2)');
                        }
            else{
                        $('td', row).css('background-color', 'rgb(255, 51, 0, 0.2)');
                        }
                     },

        select: true,
        lengthChange: false,
    buttons: [
            { extend: 'copy', editor: editor, text:  '<i class="fas fa-paste"></i>',titleAttr: 'Copiar',},
            { extend: 'excel', editor: editor, text:  '<i class="fas fa-file-excel"></i>',titleAttr: 'Guardar',},
        ]
    } );

  $('#logs_gestcol_colaboradores').on('click', 'td.dt-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    } );
} );

}(jQuery));

my current php:

<?php

include( "../lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;


Editor::inst( $db, 'logs_gestcol_colaboradores', 'log_id' )
    ->fields(
        Field::inst( 'utilizador' ),
        Field::inst( 'atividade' ),
        Field::inst( 'valores' ), // log values
        Field::inst( 'id_colaborador' ),
        Field::inst( 'data' )
    )
    ->process( $_POST )
    ->json();

I save my logs like this

                ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
                                logChange( $editor->db(), 'create', $id, $values );
                        } )
                ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
                                logChange( $editor->db(), 'edit', $id, $values );
                        } )
                ->on( 'postRemove', function ( $editor, $id, $values ) {
                                logChange( $editor->db(), 'delete', $id, $values );
                        } )

I tried to json_decode, but I need to get the "valores" first then decode it to set it as data.

Is there anyway I could show the variables instead of the whole array?

rf1234

You could do it like this for example. The values returned from the server are "user_dept", "dept_id" and "is_subsidy_register", and depending on the log version (log structure can change over time) they need to be extracted from the JSON in a different way.

Editor::inst( $db, 'log' )
->field(
  Field::inst( 'log.values AS user_dept' )
    ->getFormatter( function($val, $data, $opts) {
        $row = json_decode($val, true);
        $govNameStr = '';
        if ( isset( $row["gov"])         && 
             isset( $row["ctr_govdept"]["dept_name"] )  ) {
            foreach ($row["gov"] as $gn) {
                $govNameStr .= ( $gn["govName"] . ', ' );
            }
            $govNameStr = mb_substr($govNameStr, 0, -2);
            return $row["ctr_govdept"]["dept_name"] . ' (' . 
                   $govNameStr . ')';
        }
        if ( isset( $row["ctr_govdept_id"]) && 
             isset( $row["dept_name"])          ) {
            $govArray = getFormatterGovArray($row["ctr_govdept_id"]);
            foreach ($govArray as $gn) {
                $govNameStr .= ( $gn["govName"] . ', ' );
            }
            if ( $govNameStr > '' ) { //we still found the gov
                $govNameStr = mb_substr($govNameStr, 0, -2);
                return $row["dept_name"] . ' (' .  $govNameStr . ')';
            }
            return $row["dept_name"];
        }
        return '';
    }),     
  Field::inst( 'log.values AS dept_id' )
        ->getFormatter( function($val, $data, $opts) {
            $row = json_decode($val, true);
            if ( isset( $row["ctr_govdept_has_user"]["ctr_govdept_id"] ) ) {
                return $row["ctr_govdept_has_user"]["ctr_govdept_id"];
            }
            if ( isset( $row["ctr_govdept_id"] ) ) {
                return $row["ctr_govdept_id"];
            }
            return '';
        }),    
    Field::inst( 'log.values AS is_subsidy_register' )
        ->getFormatter( function($val, $data, $opts) {
            $row = json_decode($val, true);
            if ( isset( $row["ctr_govdept_has_user"]["is_subsidy_register"] ) ) {
                return $row["ctr_govdept_has_user"]["is_subsidy_register"];
            }
            if ( isset( $row["ctr_govdept"]["is_subsidy_register"] ) ) {
                return $row["ctr_govdept"]["is_subsidy_register"];
            }
            return "0";
        }),                 

Here are more details on the topic:
https://datatables.net/forums/discussion/comment/168173/

Helpdesk Lionesa

So far I got this working and the result is something like this:

{"id_colaborador":"1231233","nome":"Nome LastName","data_nasc":"2022-09-01","nif":"22222222","sexo":"1","nacionalidade":"11","telefone":"999999999","email":"email@email.com","morada":"MORADATESTE","cp":"9999-999","localidade":"Local","distrito":"2","dc":"1","data_atendimento":"0000-00-00","notas":""}

Editor::inst( $db, 'logs_gestcol_colaboradores', 'log_id' )
    ->fields(
        Field::inst( 'utilizador' ),
        Field::inst( 'atividade' ),
        Field::inst( 'valores' )
        ->getFormatter(function($val, $data, $db)
    {
        $valfinal = trim($val, '{');
        $valfinal = substr($valfinal, 0, -1);
        $valfinal = str_replace('"gestcol_colaboradores":', "", $valfinal);
            $obj = json_decode($valfinal);
        return $valfinal;
    }),
        Field::inst( 'id_colaborador' ),
        Field::inst( 'data' )
    )

    ->process( $_POST )
    ->json();

From this I want to join the id's to the final value, something like this:

Change "sexo":"1" to "sexo":"Feminino"

I tried doing something easy like,

$sql = "SELECT sexo FROM select_gestcol_sexo where select_sexo_id ='$obj->sexo' ";
        $result = $db-> raw()
                ->exec($sql);
        $obj->sexo = $result->fetchALL(PDO::FETCH_ASSOC);

But I think I can't do sql inside getFormatter

rf1234

Your get formatter looks wrong. The database handler isn't passed into the formatter automatically.
https://editor.datatables.net/manual/php/formatters

Something like this should work though:

->getFormatter(function($val, $data) use ($db)

Here is an example from my own coding that works:

Field::inst( 'ctr_govdept.id AS ctr_govdept.has_contracts' )->set( false )
    ->getFormatter( function($val, $data, $opts) use ($db){
        $result = $db->raw()
            ->bind( ':fk', $val )  
            ->exec( 'SELECT COUNT(*) AS ctrCount 
                       FROM ctr_has_ctr_govdept  
                      WHERE ctr_govdept_id = :fk' );
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$row["ctrCount"] ) {
            return 1;
        }
        return 0;
    }), 

Helpdesk Lionesa

It was just that simple, thank you