Insert Info from Updated New Record into an Audit Trail?

Questions related to customising nuBuilder Forte with JavaScript or PHP.

Insert Info from Updated New Record into an Audit Trail?

Unread postby pmjd » Tue Sep 14, 2021 6:32 pm

Hello,

I've been using the some modified audit trail code from this post.
viewtopic.php?f=20&t=9413
It is used in Before Save, so that any changes made to a record are logged.
Code: Select all
$nso = nuSubformObject('');

for ($i = 0; $i < count($nso->rows); $i++) {
  $p = $nso->rows[$i][0];
  $s = "SELECT * FROM $nso->table WHERE $nso->primary_key = '$p'";
  $t = nuRunQuery($s);
  $dfa = db_fetch_array($t);
  $Flds = db_field_names($nso->table);
  for ($I = 1; $I < count($nso->rows[$i]); $I++) {
    $ID = nuID();
    $Tbl = $nso->table;
    $Fld = $nso->fields[$I];
    $PK = $p;
    $Usr = "#USER_ID#";
   $Usr_name = "#USER_NAME#";
   $usr_name = empty($Usr_name) ? "System Admin" : $Usr_name;
   $lot = "#mpd_lot_number#";
   $nsoV = $dfa[$nso->fields[$I]];
    $NV = $nso->rows[$i][$I];
   if ($PK == "-1") {
    if ($nsoV != $NV && in_array($Fld, $Flds)) {
      $S = "INSERT INTO lotgen_audit_trail (lotgen_audit_trail_id, table_name, record_identifier, field_name, pk_value, userid, user_name, old_value, new_value)"
            ."VALUES ('$ID', '$Tbl', 'New','$Fld', '$PK',  '$Usr', '$usr_name', '$nsoV', '$NV')";
      nuRunQuery($S);
      }
     }
   else {
    if ($nsoV != $NV && in_array($Fld, $Flds)) {
      $S = "INSERT INTO lotgen_audit_trail (lotgen_audit_trail_id, table_name, record_identifier, field_name, pk_value, userid, user_name, old_value, new_value)"
            ."VALUES ('$ID', '$Tbl', '$lot','$Fld', '$PK',  '$Usr', '$usr_name', '$nsoV', '$NV')";
      nuRunQuery($S);
      }
     }

    }
  }

For new records I've set it so that the -1 value generated by a new record is instead displayed as "New" in another column. For editing of existing records the lot number is inserted instead.

This all works well so far...

However, the lot number assigned to the records is created by code in the After Save section (by taking an autonumber and prepending a prefix to it, as shown below), so this information is not logged.
Code: Select all
$mpdlotnumber = "
   UPDATE mvt_prepared_diluent
   SET mpd_lot_number = CONCAT('MSL-',LPAD(mpd_autonumber,4,'0'))
   WHERE mpd_lot_number IS NULL AND mvt_prepared_diluent_id = ?
";
nuRunQuery($mpdlotnumber, ["#RECORD_ID#"]);


Ideally like to insert the result of this update event into the audit trail too, so that the new lot number is logged in the audit trail. The code would only ever be run once on records creation.

I've tried the following code but neither are working.
Code: Select all
$ID = nuID();
$Tbl = "#table#";
$Usr = "#USER_ID#";
$Usr_name = "#USER_NAME#";

$mpdlotnumber = "
   UPDATE mvt_prepared_diluent
   SET mpd_lot_number = CONCAT('MSL-',LPAD(mpd_autonumber,4,'0'))
   INSERT INTO  lotgen_audit_trail (lotgen_audit_trail_id, table_name, record_identifier, field_name, pk_value, userid, user_name, old_value, new_value)
       VALUES ('$ID', '$Tbl', 'New Lot','mpd_lot_number', '$PK',  '$Usr', '$usr_name', 'N/A', '#mpd_lot_number#')
   WHERE mpd_lot_number IS NULL AND mvt_prepared_diluent_id = ?
   
";
nuRunQuery($mpdlotnumber, ["#RECORD_ID#"]);

nuDebug(nuHash());


and

Code: Select all
$mpdlotnumber = "
   UPDATE mvt_prepared_diluent
   SET mpd_lot_number = CONCAT('MSL-',LPAD(mpd_autonumber,4,'0'))
   WHERE mpd_lot_number IS NULL AND mvt_prepared_diluent_id = ?
   
";
nuRunQuery($mpdlotnumber, ["#RECORD_ID#"]);

$nso = nuSubformObject('');

for ($i = 0; $i < count($nso->rows); $i++) {
  $p = $nso->rows[$i][0];
  $s = "SELECT * FROM $nso->table WHERE $nso->primary_key = '$p'";
  $t = nuRunQuery($s);
  $dfa = db_fetch_array($t);
  $Flds = db_field_names($nso->table);
  for ($I = 1; $I < count($nso->rows[$i]); $I++) {
    $ID = nuID();
    $Tbl = $nso->table;
    $Fld = $nso->fields[$I];
    $PK = $p;
    $Usr = "#USER_ID#";
   $Usr_name = "#USER_NAME#";
   $usr_name = empty($Usr_name) ? "System Admin" : $Usr_name;
   $lot = "#mpd_lot_number#";
    if ($nsoV != $NV && in_array($Fld, $Flds)) {
      $S = "INSERT INTO lotgen_audit_trail (lotgen_audit_trail_id, table_name, record_identifier, field_name, pk_value, userid, user_name, old_value, new_value)"
            ."VALUES ('$ID', '$Tbl', 'New','$Fld', '$PK',  '$Usr', '$usr_name', 'N/A', '$lot')";
      nuRunQuery($S);
      }
    }
  }
nuRunQuery($nso, ["#RECORD_ID#"]);


Can anyone help point me in the right direction?

Thanks,
Paul
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5

Re: Insert Info from Updated New Record into an Audit Trail?

Unread postby kev1n » Tue Sep 14, 2021 6:48 pm

What if you use BS to set the lot number with nuSetNuDataValue instead of AS?
kev1n
nuBuilder Team
 
Posts: 2008
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Insert Info from Updated New Record into an Audit Trail?

Unread postby pmjd » Tue Sep 14, 2021 7:02 pm

The autonumber is not set until After Save, it's field is blank when I checked the available BS hash cookies.

Or is there some way of including/nesting an UPDATE statement to also insert values into the audit trail as well as updating the main table in the After Save section?
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5

Re: Insert Info from Updated New Record into an Audit Trail?

Unread postby kev1n » Wed Sep 15, 2021 8:40 am

Maybe like this? Retrieve mpd_lot_number with a SELECT statement in AS.

Then UPDATE lotgen_audit_trail and set the lot number of the last inserted record. You probably need a timestamp column to identify it.
kev1n
nuBuilder Team
 
Posts: 2008
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Insert Info from Updated New Record into an Audit Trail?

Unread postby pmjd » Wed Sep 15, 2021 6:42 pm

Still not sure how to make sure it is a once off event though, as the AS code will run everytime and it doesn't need to as the lot number is saved as part of the audit trail code.

Can you set SQL so that it evaluates a WHERE condition first (i.e. the lot number field is blank) which would then trigger the update to complete the lot number table and then insert the same info to the audit trail table? If the lot number field is populated the code won't run?

Alternatively timestamps which match are generated for both the audit trail entries for the new lot number and the table where the lot number is created. So probably easiest to manually cross reference between the two when the need arises.
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5

Re: Insert Info from Updated New Record into an Audit Trail?

Unread postby kev1n » Fri Sep 17, 2021 12:20 am

How about setting a server variable in BS that contains the log's table PK?

Code: Select all
$_SERVER["log_id"] =  $ID;


And in AS retrieve it to update the log table with the lot number

Code: Select all
$log_id = $_SERVER["log_id"];


(This will work as long as just one row has to be updated/no subform is used)
kev1n
nuBuilder Team
 
Posts: 2008
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5


Return to Custom Code

Who is online

Users browsing this forum: No registered users and 5 guests