ad_audit_trail

ad_audit_trail   db   id_list   audit_table_name   main_table_name   id_column_list   { columns_not_reported "" }   { start_date "" }   { end_date "" }   { restore_url "" }

Returns an HTML fragment showing changes to one row in the OLTP system between the times start_date and end_date (YYYY-MM-DD HH24:MI:SS). There will be one section for each row in the audit table and a single section for the occurrence of id (must be unique) in main_table, the entire affair sorted by time (descending). If a restore_url is provided, a link will appear next to each non-delete section to the restore url with the current rowid and ad_audit_trail arguments.

Source code:

    # These values will be part of an audit entry description
    # and do not need to be reported seperately
    lappend columns_not_reported modifying_user_name
    lappend columns_not_reported last_modifying_user
    lappend columns_not_reported last_modified
    lappend columns_not_reported modified_ip_address
    lappend columns_not_reported delete_p
    lappend columns_not_reported rowid

    # HTML string to be returned at the end of the proc
    set return_string ""

    # The date restrictions should only be added if start_date or end_date
    # is not empty
    set date_clause ""
    if { ![empty_string_p $end_date] } {
	append date_clause "and last_modified < to_date('$end_date','YYYY-MM-DD HH24:MI:SS')"
    }
    if { ![empty_string_p $start_date] } {
	append date_clause "\nand last_modified > to_date('$start_date','YYYY-MM-DD HH24:MI:SS')"
    }

    # Generate main and audit table restrictions for 
    # the ids in the id columns
    set main_table_id_clause ""
    set audit_table_id_clause ""
    set count 0

    # check that the ids are not going to cause a problem
    set id_list [DoubleApos $id_list]
    foreach id $id_list {
	set id_column [lindex $id_column_list $count]
	incr count

	append main_table_id_clause "\nand $main_table_name.$id_column = '$id'"
	append audit_table_id_clause "\nand $audit_table_name.$id_column = '$id'"
    }

    # Get the entries in the audit table
    set selection [ns_db select $db "select 
 $audit_table_name.*, $audit_table_name.rowid,
 to_char($audit_table_name.last_modified,'Mon DD, YYYY HH12:MI AM')
  as last_modified,
 users.first_names || ' ' || users.last_name as modifying_user_name
from $audit_table_name, users 
where users.user_id = $audit_table_name.last_modifying_user
$audit_table_id_clause
$date_clause
order by $audit_table_name.last_modified asc"]

    # The first record displayed may not represent an insert if 
    # start_date is not empty. So display the first record as an update
    # if start_date is not empty.
    if { ![empty_string_p $start_date] } {
	# Not all records will be displayed, so first record may not be
	# an insert.
	set audit_count 1
    } else {
	# All records are being displayed so first record is an insert
	set audit_count 0
    }

    # used to keep track of previous record's data so that only updated
    # information is displayed.
    set old_selection [ns_set create old_selection]

    while { [ns_db getrow $db $selection] } {
       ad_audit_process_row
       append return_string $audit_entry
    }

    # get the current records
    set selection [ns_db select $db "
select 
 $main_table_name.*,  
 users.first_names || ' ' || users.last_name as modifying_user_name, 
 to_char($main_table_name.last_modified,'Mon DD, YYYY HH12:MI AM')
  as last_modified
from $main_table_name, users 
where users.user_id = $main_table_name.last_modifying_user
$main_table_id_clause
$date_clause
order by $main_table_name.last_modified asc"]

    # tell ad_audit_process_row that this is not a deleted row
    set delete_p "f"

    while { [ns_db getrow $db $selection] } {
	ad_audit_process_row    
	append return_string $audit_entry
    }

    return $return_string