Get logChange values to variables

Get logChange values to variables

Helpdesk LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1

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?

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,950Questions: 87Answers: 416
    edited October 2022

    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 LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1

    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

  • rf1234rf1234 Posts: 2,950Questions: 87Answers: 416
    Answer ✓

    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 LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1

    It was just that simple, thank you :smile:

Sign In or Register to comment.