using postEdit to update main table fails

using postEdit to update main table fails

YoDavishYoDavish Posts: 123Questions: 46Answers: 3
edited August 2021 in DataTables 1.9

I have editor v 1.9.6.
Within the postEdit fucntion on my server side PHP page, there is a logChange function that will save the old and new value for an audit log onto a new table, this works perfectly fine. However, I want to add an additional function to perform updates to the "Task" table depending on 2 column values. For instance,

If "TaskDefId" column = "2" and "Status" column = "complete"
it would call a new function "handleTaskStatusChange" to update the Task table to "TaskDefId" column = "8" and "Status" column = "created", however when I run this, DataTables stalls and does not update "TaskDefId" = "8". If debug the sql and parameters, everything looks correct for the update and putting in a file_put_contents() I see it reaches the function correctly. Additionally, if I comment out "executeSql" with in the "updateTaskTable" function, then it runs smoothly again. I'm assuming it's a timing or synchronization thing for why this fails to update? How can I call this update in handleTaskStatusChange function, if not in the "postEdit" to be performed? Code is below:

<?php
session_start();
// Task Workflow Handler
include("taskWorkflowHandler.php");

// DataTables PHP library
include ("../lib/datatables.inc.php");
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
    
$prevValues = [];
// runs only one per row

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

function logChange( $db, $table, $action, $id, $values) {
    date_default_timezone_set('America/Chicago'); 
    global $prevValues;
    switch ($action) {
        case "create":
            $oldValues = [];
            $newValues = $values;
            break;
        case "edit":
            $oldValues = array_intersect_key(array_diff_assoc($prevValues,$values[$table]),array_diff_assoc($values[$table],$prevValues));
            $newValues = array_intersect_key(array_diff_assoc($values[$table],$prevValues),array_diff_assoc($prevValues,$values[$table]));
            break;
        case "delete":
            $oldValues = $prevValues;
            $newValues = [];
            break;
    }
    
    if (!empty($oldValues) || !empty($newValues)){
        foreach($oldValues as $oKey => $oValue){
            foreach($newValues as $nKey => $nValue){
                if($oKey == $nKey){ // only insert if the keys match
                    $db->insert( 'taskaudit', array(
                        'user'      => isset($_SESSION['user']), 
                        'action'    => $action,
                        'oldValue'  => $oValue,
                        'newValue'  => $nValue,
                        'table'     => $table,
                        'rowId'     => $id,
                        'column'    => $oKey,
                        'date'      => date('Y-m-d H:i:s')
                    ));
                }
            }
        }
        if(array_key_exists('statusDefId', $newValues) && $action == 'edit'){
            handleTaskStatusChange($oldValues, $newValues, $prevValues, $id);
        }
    }
}

// Build our Editor instance and process the data coming from _POST
    $editor = Editor::inst( $db, 'task', 'id');
    $tableServerFieldApplicationId = 'tableServerFields'."_".$_SESSION['applicationPageId'];
    if(isset($_SESSION[$tableServerFieldApplicationId])){
        foreach($_SESSION[$tableServerFieldApplicationId] as $fieldRow){
            $editor->fields(Field::inst($fieldRow));
        }
    }
    
    // Pre functions
    // preEdit:  Pre-row update event - triggered before the row / page data is updated.
    $editor->on( 'preEdit', function ( $editor, $id, $values){
        getPrevValues($editor->db(), $editor->table()[0], $id);
     });
    $editor->on( 'preRemove', function ( $editor, $id, $values){
        getPrevValues($editor->db(), $editor->table()[0], $id);
     });
    //Post functions
    // postEdit:  Post-row edit event - triggered after the row / page has been updated.
    $editor->on( 'postEdit', function ( $editor, $id, $values, $row ){
        logChange( $editor->db(), $editor->table()[0], 'edit', $id, $values );
     });
    $editor->process( $_POST );
    $editor->json();


// taskWorkflowHandler.php page:
$connectPdoResults = connectPdo();

function handleTaskStatusChange($oldValue, $newValue, $taskData, $taskId){
    global $connectPdoResults;
    $errorCode = "";
    $errMsg = "";
    
    // get taskWorkflow details
    $taskWorkflowResult = getTaskWorkflow($connectPdoResults['pdo'], $taskData, $newValue['statusDefId']);
    $twfRow = $taskWorkflowResult['data'];

    if($taskWorkflowResult['errorCode']){
        $errorCode = $taskWorkflowResult['errorCode'];
        $errMsg .= $taskWorkflowResult['errMsg'];
    }
    // If createNewTask is 0
    if($twfRow['createNewTask'] == 0){
        $updateTaskResult = updateTaskTable($connectPdoResults['pdo'], $twfRow, $taskId);
        if($updateTaskResult['errorCode']){
            $errorCode = $updateTaskResult['errorCode'];
            $errMsg .= $updateTaskResult['errMsg'];
        }
    }
}

function updateTaskTable($pdo, $taskworkflowData, $taskId){
    $taskArgs = [];
    $errorCode = "";
    $errMsg = "";
    
    $updateTaskSql = 'update prosystem_dev.task set taskDefId = ? ';
    $taskArgs[] = $taskworkflowData['nextTaskDefId'];
    if($taskworkflowData['nextStatusDefId']){
        $updateTaskSql .= ', statusDefId = ? ';
        $taskArgs[] = $taskworkflowData['nextStatusDefId'];
    }
    $updateTaskSql .= ' where id = ?';
    $taskArgs[] = $taskId;
    $updateTaskResult = executeSql($pdo, $updateTaskSql, $taskArgs);
    if($updateTaskResult['errorCode']){
        $errorCode = $updateTaskResult['errorCode'];
        $errMsg = $updateTaskResult['errMsg'];
    }
    // return if errors exist
    return ['errorCode'=>$errorCode, 'errMsg'=>$errMsg];
}
?>

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    I'm assuming it's a timing or synchronization thing for why this fails to update?

    It will be a locking issue. Editor's PHP libraries operate inside a transaction so that if anything goes wrong, then the db tables will just rollback to the state they were in before. That means that you can't read the changes until the transaction is committed unless you are in the same transaction.

    So - two options:

    1. Use Editor->transaction(false) to disable transactions, or
    2. Use Editor->db() to get the current database transaction object and reuse that.

    Allan

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    Thanks @allan , I added the "$editor->transaction(false);" right before the "$editor->process( $_POST );" and that solved the issue!

Sign In or Register to comment.