Audit logging editor log fails on "where field" if updated

YoDavish

I'm using editor and modified some code I've found in the datatables logging documentation and forums:

• https://editor.datatables.net/manual/php/events#Logging-changes)
• https://datatables.net/forums/discussion/46968

It logs almost all edits, except for the one on the "Completed" column which I'm using a condition on for the SQL
->where( 'Completed', NULL )

The errors I'm getting are indicating that the array of values is null.

array_diff_assoc(): Expected parameter 2 to be an array, null given
array_intersect_key(): Expected parameter 1 to be an array, null given

I'm assuming that the changes occur first before it can capture the values to be sent to the logging?

If I disable the where condition, then it's able to save the edits.

Additionally, I've enabled the autoFill functionality, and when I try to edit multiple rows, logging is reacting a bit strange. My steps were, change a column (ex row id 1) to a new value, select out of the cell to allow for change to be submitted. Select cell and drag and click over multiple cells (row id 1-5) of the same column. The results in the logging table show that the first and very last records (row id 1 and 5) have their old and new values saved correctly. However, rows 2,3, and 4 logged all of its column data rather than what was changed only.

Any help on this would be greatly appreciated.

Here is my code below:

$prevValues = [];
function getPrevValues ( $db, $table, $id ) {
    global $prevValues;
    $prevValues = $db->select( $table, '*', [ 'id' => $id ] )->fetch();
}

function logChange ( $db, $table, $action, $id, $values ) {
    global $prevValues;
    switch ($action) {
        case "create":
            $old_values = [];
            $new_values = $values;
            break;
        case "edit":
            $old_values = array_intersect_key(array_diff_assoc($prevValues,$values),array_diff_assoc($values,$prevValues));
            $new_values = array_intersect_key(array_diff_assoc($values,$prevValues),array_diff_assoc($prevValues,$values));
            break;
        case "delete":
            $old_values = $prevValues;
            $new_values = [];
            break;
    }
    
    if (!empty($old_values) || !empty($new_values)){
        $db->insert( 'audit', array(
            'user'      => isset($_SESSION['currentUser']) ? $_SESSION['currentUser'] : NULL,
            'action'            => $action,
            'oldValue'          => json_encode($old_values),
            'newValue'  => json_encode($new_values),
            'table'             => $table,
            'row'               => $id,
            'date'      => date('c')
        ));
    }
}
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'correctedaddress', 'id' )
    ->fields(
        Field::inst('AssignedTo'),
        Field::inst('Note'),
        Field::inst('Completed'),
        Field::inst('id'),
        Field::inst('originalFileDate'),
        Field::inst('processedDate'),
        Field::inst('originalFileName'),
        Field::inst('currentFileName'),
        Field::inst('originalFilePath'),
        Field::inst('currentFilePath'),
        Field::inst('sharepointFilePath'),
        Field::inst('accessionNumber'),
        Field::inst('documentType'),
        Field::inst('createdBy'),
        Field::inst('createdTime'),
        Field::inst('modifiedBy'),
        Field::inst('modifiedTime'),
        Field::inst('inactivatedBy'),
        Field::inst('inactivatedTime'),
        Field::inst('scanner'),
        Field::inst('correctedFlag'),
        Field::inst('user'),
        Field::inst('folderName'),
        Field::inst('ImageFile'),
        Field::inst('PageCount'),
        Field::inst('BatchID'),
        Field::inst('case'),
        Field::inst('checkNumber'),
        Field::inst('fileName'),
        Field::inst('depositDate'),
        Field::inst('depositAmount'),
        Field::inst('postingDate'),
        Field::inst('batchAmount'),
        Field::inst('requestDate'),
        Field::inst('name'),
        Field::inst('clientAccount'),
        Field::inst('account'),
        Field::inst('batchNumber'),
        Field::inst('barcode'),
        Field::inst('mrn'),
        Field::inst('ssn'),
        Field::inst('addlCases'),
        Field::inst('hospitalName'),
        Field::inst('companyName')
    )
    ->where( 'Completed', NULL )
    // Pre functions
    ->on( 'preEdit', function ( $editor, $id, $values ) {
        getPrevValues($editor->db(), $editor->table()[0], $id);
    })
    ->on( 'preRemove', function ( $editor, $id, $values ) {
        getPrevValues($editor->db(), $editor->table()[0], $id);
    })
    
    // Post functions
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), $editor->table()[0], 'create', $id, $row);
    })
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), $editor->table()[0], 'edit', $id, $row );
    })
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), $editor->table()[0], 'delete', $id, $row );
    })
    ->process( $_POST )
    ->json();
?>

allan

I've enabled the autoFill functionality, and when I try to edit multiple rows, logging is reacting a bit strange

The events happen once per row being edited. So you would need a different $$prevValues for each row being edited / deleted (probably keyed by id).

I'm assuming that the changes occur first before it can capture the values to be sent to the logging?

You are getting the values in a pre* event handler, so that will run before the edit or delete happens. I'd suggest logging what the value you are reading from the database is - perhaps include also the id to make sure it is what is expected.

Allan

YoDavish

@allan Thanks for the reply, I'm trying to work on the first part that you suggested > "The events happen once per row being edited. So you would need a different $$prevValues for each row being edited / deleted (probably keyed by id)", however, I'm having a bit of trouble getting started on this any help would be greatly appreciated.

YoDavish

Found the problem, in case anyone else needed this information. The column that the condition was filtering on "->where( 'Completed', NULL )". When I changed the column to default to a value of "0" and updated all the "NULL" to "0". I no longer got any errors, and it was logging correctly even with autofill on.

allan

Hi,

Thanks for the update. Good to hear you've got it working!

Allan