Insert Info from Updated New Record into an Audit Trail?

[pmjd]

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

[kev1n]

What if you use BS to set the lot number with nuSetNuDataValue instead of AS?

[pmjd]

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?

[kev1n]

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.

[pmjd]

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.

[kev1n]

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)

[pmjd]

Thanks kev1n, will give it a try.