custom_userhistory: lots of workgentoo-5.0.4.2

- Add Audit log section based on audit_log table
- HTML improvements
- Use tables for output, with consistent style
- DBD bind argument style
- Query formatting for easier debugging

Signed-off-by: Robin H. Johnson <robbat2@gentoo.org>

1 files changed, 234 insertions, 69 deletions

diff --git a/custom_userhistory.cgi b/custom_userhistory.cgi
index 85e01cb37..4d4ece704 100755
--- a/custom_userhistory.cgi
+++ b/custom_userhistory.cgi
@@ -13,15 +13,16 @@ my $cgi       = Bugzilla->cgi;
 my $vars      = {};
 my $myuser = Bugzilla->login(LOGIN_REQUIRED);
 my $dbh       = Bugzilla->switch_to_shadow_db();
-my @bindValues;
 my ($query, $matchstr, $userid, $limit, $login_name);
 
 print $cgi->header();
 
 $matchstr = $cgi->param('matchstr');
 $userid = $cgi->param('userid');
+$userid = undef unless defined($userid) and $userid =~ /^\d+$/;
 if(!defined($matchstr) and !defined($userid)) {
-	print "No search parameters specified!<br/>";
+	print "No search parameters specified!<br/>\n";
+	print "Put <tt>matchstr</tt> or <tt>userid</tt> in the URL parameters.<br/>\n";
 	exit(0);
 }
 exit 0 if !defined($matchstr) and !defined($userid);
@@ -41,99 +42,263 @@ if(!$userid || !$login_name) {
 	exit(0);
 }
 
-my @bindValues2;
-$query = sprintf
-	'(SELECT bug_id, bug_when, fielddefs.name AS field '.
-		'FROM bugs_activity JOIN fielddefs ON bugs_activity.fieldid=fielddefs.id '.
-		'WHERE who=? '.
-		'ORDER BY bug_when DESC '.
-		'LIMIT %d) '.
-	'UNION '.
-	'(SELECT bug_id, bug_when, \'ZZcomment #\' AS field '.
-		'FROM longdescs '.
-		'WHERE who=? '.
-		'ORDER BY bug_when DESC '.
-		'LIMIT %d) '.
-	'UNION '.
-	'(SELECT bug_id, creation_ts AS bug_when, CONCAT(\'ZZattachment #\', attach_id)  AS field '.
-		'FROM attachments '.
-		'WHERE submitter_id=? '.
-		'ORDER BY creation_ts DESC '.
-		'LIMIT %d) '.
-	'ORDER BY bug_when DESC '.
-	'LIMIT %d',
-	$limit,$limit,$limit,$limit;
-
-push(@bindValues2, $userid);
-push(@bindValues2, $userid);
-push(@bindValues2, $userid);
-
-#print Dumper($vars);
-printf "%s<br/>",$login_name;
+$query = qq{
+(SELECT
+		bug_id,
+		bug_when,
+		fielddefs.name AS field
+	FROM
+		bugs_activity
+		JOIN fielddefs ON bugs_activity.fieldid=fielddefs.id
+	WHERE
+		who=?
+	ORDER BY
+		bug_when DESC
+	LIMIT $limit)
+UNION
+(SELECT
+		bug_id,
+		bug_when,
+		'ZZcomment #' AS field
+	FROM
+		longdescs
+	WHERE
+		who=?
+	ORDER BY
+		bug_when DESC
+	LIMIT $limit)
+UNION
+(SELECT
+		bug_id,
+		creation_ts AS bug_when,
+		CONCAT('ZZattachment #', attach_id)  AS field
+	FROM
+		attachments
+	WHERE
+		submitter_id=?
+	ORDER BY
+		creation_ts DESC
+	LIMIT $limit)
+ORDER BY bug_when DESC
+LIMIT $limit};
 my $actions = $dbh->selectall_arrayref(
     $query,
     { Slice => {} },
-	@bindValues2
+	($userid, $userid, $userid),
 );
 
+#print Dumper($vars);
+printf "<h1>Custom User History: %s</h1>\n", $login_name;
+print "<table>\n";
+printf "<tr><th>login_name</th><td>%s</td></tr>\n",$login_name;
+printf "<tr><th>userid</th><td>%s</td></tr>\n",$userid;
+print "</table>\n";
+
+sub show_bug_url {
+	return "/show_bug.cgi?id=".shift;
+}
+
+print q{
+<hr/>
+<h2>Bug History</h2>
+<table>
+	<tr>
+		<th>Timestamp</th>
+		<th>BugID</th>
+		<th>Field</th>
+	</tr>
+};
 my $counter = 0;
 foreach my $row (@$actions) {
-	printf "<a href=\"%9d\">%9d</a>: %s %s<br/>", $row->{'bug_id'}, $row->{'bug_id'}, $row->{'bug_when'}, $row->{'field'};
 	$counter++;
+	my $url = show_bug_url($row->{'bug_id'});
+	(my $message = qq{
+		<tr>
+			<td>$row->{'bug_when'}</td>
+			<td><a href="${url}">$row->{'bug_id'}</a></td>
+			<td>$row->{'field'}</td>
+		</tr>
+	}) =~ s/^\t{1}//mg;
+	print $message;
 }
-printf "History Done. Limit=%d Count=%d<br/><br/>",$limit,$counter;
-
-$query = 'SELECT
-p2.userid AS grantor_id, p1.userid AS grantee_id,
-p2.login_name AS grantor, p1.login_name AS grantee,profiles_when,oldvalue,newvalue
-FROM profiles p1
-JOIN profiles_activity ON p1.userid=profiles_activity.userid
-JOIN profiles p2 ON p2.userid=who
-WHERE p1.userid = ? OR p2.userid = ?
-ORDER BY profiles_when';
-my @bindValues3;
-push(@bindValues3, $userid);
-push(@bindValues3, $userid);
+printf "</table>.\nHistory Done.\nLimit=%d Count=%d<br/><br/>",$limit,$counter;
+
+$query = q{
+SELECT
+	p2.userid AS grantor_id,
+	p1.userid AS grantee_id,
+	p2.login_name AS grantor,
+	p1.login_name AS grantee,
+	profiles_when,
+	oldvalue,
+	newvalue
+FROM
+	profiles p1
+	JOIN profiles_activity ON p1.userid=profiles_activity.userid
+	JOIN profiles p2 ON p2.userid=who
+WHERE
+	FALSE
+	OR p1.userid = ?
+	OR p2.userid = ?
+ORDER BY
+	profiles_when};
 $actions = $dbh->selectall_arrayref(
     $query,
     { Slice => {} },
-	@bindValues3
+	($userid, $userid),
 );
 
-printf "Applied to %s:<br/>",$login_name;
+print "<hr/><h2>Profile Activity</h2>\n";
+printf "<h3>Applied to %s:</h3>\n",$login_name;
+print q{
+<table>
+	<tr>
+		<th>Timestamp</th>
+		<th>Grantor</th>
+		<th>Grantee</th>
+		<th>Oldvalue</th>
+		<th>Newvalue</th>
+	</tr>
+};
 foreach my $row (@$actions) {
-	printf "%s: by %s: %s%s %s%s<br/>", $row->{'profiles_when'}, $row->{'grantor'}, $row->{'oldvalue'} ? '-' : '', $row->{'oldvalue'}, $row->{'newvalue'}? '+' : '', $row->{'newvalue'} if $row->{'grantee_id'} == $userid;
+    next unless $row->{'grantee_id'} == $userid;
+	(my $message = qq{
+		<tr>
+			<td>$row->{'profiles_when'}</td>
+			<td>$row->{'grantor'}</td>
+			<td>$row->{'grantee'}</td>
+			<td>$row->{'oldvalue'}</td>
+			<td>$row->{'newvalue'}</td>
+		</tr>
+	}) =~ s/^\t{1}//mg;
+	print $message;
 }
-printf "<br/>";
+print "</table>\n";
 
-printf "Applied by %s:<br/>",$login_name;
+printf "<h3>Applied by %s:</h3>\n",$login_name;
+print q{
+<table>
+	<tr>
+		<th>Timestamp</th>
+		<th>Grantor</th>
+		<th>Grantee</th>
+		<th>Oldvalue</th>
+		<th>Newvalue</th>
+	</tr>
+};
 foreach my $row (@$actions) {
-	printf "%s: to %s: %s%s %s%s<br/>", $row->{'profiles_when'}, $row->{'grantee'}, $row->{'oldvalue'} ? '-' : '', $row->{'oldvalue'}, $row->{'newvalue'}? '+' : '', $row->{'newvalue'} if $row->{'grantor_id'} == $userid;
+    next unless $row->{'grantor_id'} == $userid;
+	(my $message = qq{
+		<tr>
+			<td>$row->{'profiles_when'}</td>
+			<td>$row->{'grantor'}</td>
+			<td>$row->{'grantee'}</td>
+			<td>$row->{'oldvalue'}</td>
+			<td>$row->{'newvalue'}</td>
+		</tr>
+	}) =~ s/^\t{1}//mg;
+	print $message;
 }
-printf "<br/>";
-
-$query = 'SELECT
-p1.userid AS watcher_id, p2.userid AS watched_id,
-p1.login_name AS watcher, p2.login_name AS watched
-FROM profiles p1
-JOIN watch ON p1.userid=watch.watcher
-JOIN profiles p2 ON p2.userid=watch.watched
-ORDER BY watcher,watched
-';
+print "</table>\n";
+
+$query = q{
+SELECT
+	p1.userid AS watcher_id,
+	p2.userid AS watched_id,
+	p1.login_name AS watcher,
+	p2.login_name AS watched
+FROM
+	profiles p1
+	JOIN watch ON p1.userid=watch.watcher
+	JOIN profiles p2 ON p2.userid=watch.watched
+WHERE
+	FALSE
+	OR p1.userid = ?
+	OR p2.userid = ?
+ORDER BY
+	watcher,watched
+};
 $actions = $dbh->selectall_arrayref(
     $query,
     { Slice => {} },
+	($userid, $userid),
 );
-printf "Watchers of %s:<br/>", $login_name;
+print "<hr/><h2>Watch status</h2>\n";
+printf "<h3>Watchers of %s:</h3>\n", $login_name;
 foreach my $row (@$actions) {
-printf "%s<br/>", $row->{'watcher'} if $row->{'watched_id'} == $userid;
+printf "%s<br/>\n", $row->{'watcher'} if $row->{'watched_id'} == $userid;
 }
-printf "<br/>";
+printf "<br/>\n";
 
-printf "Watched by %s:<br/>", $login_name;
+printf "<h3>Watched by %s:</h3>", $login_name;
 foreach my $row (@$actions) {
-printf "%s<br/>", $row->{'watched'} if $row->{'watcher_id'} == $userid;
+printf "%s<br/>\n", $row->{'watched'} if $row->{'watcher_id'} == $userid;
 }
-printf "<br/>";
+printf "<br/>\n";
+
 
-printf "Done.<br/>";
+
+$query = q{
+SELECT
+	user_id,
+	class,
+	object_id,
+	field,
+	at_time
+FROM
+	audit_log
+WHERE
+	FALSE
+	OR user_id=?
+	OR (class = 'Bugzilla::User' AND object_id=?)
+ORDER BY
+	at_time
+};
+my $audits = $dbh->selectall_arrayref(
+    $query,
+    { Slice => {} },
+	($userid, $userid),
+);
+
+print "<hr/><h2>Audit log</h2>";
+printf "<h3>Changes by %s:</h3>\n", $login_name;
+print q{
+<table>
+	<tr>
+		<th>Timestamp</th>
+		<th>UserID</th>
+		<th>Class/ID</th>
+		<th>Field</th>
+	</tr>
+};
+foreach my $row (@$audits) {
+	next unless $row->{'user_id'} == $userid;
+	(my $message = qq{
+		<tr>
+			<td>$row->{'at_time'}</td>
+			<td>$row->{'user_id'}</td>
+			<td>$row->{'class'}/$row->{'object_id'}</td>
+			<td>$row->{'field'}</td>
+		</tr>
+	}) =~ s/^\t{1}//mg;
+	print $message;
+}
+print "</table>\n";
+
+printf "<h3>Changes to %s:</h3>", $login_name;
+print "<table>\n";
+print "<tr><th>Timestamp</th><th>UserID</th><th>Class/ID</th><th>Field</th></tr>\n";
+foreach my $row (@$audits) {
+	next unless $row->{'object_id'} == $userid && $row->{'class'} eq 'Bugzilla::User';
+	(my $message = qq{
+		<tr>
+			<td>$row->{'at_time'}</td>
+			<td>$row->{'user_id'}</td>
+			<td>$row->{'class'}/$row->{'object_id'}</td>
+			<td>$row->{'field'}</td>
+		</tr>
+	}) =~ s/^\t{2}//mg;
+	print $message;
+}
+print "</table>\n";
+printf "<hr/>Done.<br/>\n";