Extension:DocumentApproval

Goal
Allows electronic signing of pages with approval versioning.

Motivation
This extension allows the users writing a page to put the approval need (managers, consultants, team members) on it, and also allow the users sign the page, certifying that they already read the page and signed it.

Some times you need to show that a certain group of people 'certify' that what you wrote is right. It means that this 'approved' page is certified for use (like a checkpoint in a document).

Requesting Approval
To request an approval in a page use the tag like this:

username1; job/role1 username2; job/role2 usernameN; job/roleN

When the page is saved, the extension updates de approval request table and put it in the page. For example:

Jgramacho; Project Leader

Will result in:

When the user signs the page, the table will show like:

Approval Versioning
Every page for approval has three numbers about its revision: the version, the subversion and the editing counter.

When you have only 'minor edits' between two document approvals, its subversion will be added by one.

When you have at least one non 'minor edits' between two document approvals, its version will be added by one.

The editing counter registers how many times the page was changed since last approval. In all fully signed versions the editing counter is zero!

Signing
If a page has an approval request to the user, a 'sign' tab will be displayed. By clicking on this tab, the user will trigger the page signature.

Also, to sign a page you may use the 'sign' action (just like the 'edit' action).

Download instructions
Please cut and paste the codes found below and place it in. Note: $IP stands for the root directory of your MediaWiki installation, the same directory that holds LocalSettings.php.

Remember: there are three files to install:
 * DocumentApproval.php
 * DocumentApproval.body.php
 * DocumentApproval.i18n.php

Installation
First of all, check the MySQL database table creation script below. You must substitute the  by your database table prefix before running it on your database.

To install this extension, add the following to LocalSettings.php:

Database Script
Remember to substitute the  by your table prefix!

Code
New versions are maintained at Google Code Hosting: http://code.google.com/p/mediawiki-document-approval/

DocumentApproval.body.php
\n";   $output .= "\n";

return $output; }

function fnSign($action, $article) {   global $wgOut;

daTableCheck; if ($action == 'sign') {       $title = $article->getTitle;

$revision = Revision::newFromTitle($title); $revisionid = $revision->getId; $pageid = $revision->getPage; $user = User::newFromSession; $userid = $user->getID;

$dbr = wfGetDB( DB_SLAVE );

// Check if this user can sign the document $sql = "SELECT user_id FROM ". $dbr->tableName('approval_request'); $sql .= " WHERE page_id = ". $pageid; $sql .= " AND user_id = ". $userid;

$reqUserId = "";

$res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {           $row = $dbr->fetchRow($res); $reqUserID = $row[0]; }       $dbr->freeResult($res);

if ($reqUserID != $userid) {           $wgOut->addWikiText(wfMsgDA("daNotNecessary")); return false; }

// Check if document is already signed $sql = "SELECT date FROM ". $dbr->tableName('approval'); $sql .= " WHERE rev_page = ". $pageid; $sql .= " AND user_id = ". $userid; $sql .= " AND rev_id = ". $revisionid;

$signDate = "";

$res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {           $row = $dbr->fetchRow($res); $signDate = $row[0]; }       $dbr->freeResult($res);

// If it is not signed yet if ($signDate == "") // Sign it       { $sql = "INSERT INTO ". $dbr->tableName('approval'); $sql .= " (rev_id, rev_page, user_id) VALUES"; $sql .= " ($revisionid, $pageid, $userid)"; $res = $dbr->query( $sql, __METHOD__ );

// Check how many already signed it $sql = "SELECT count(*) as approvals FROM ". $dbr->tableName('approval'); $sql .= " WHERE rev_page = ". $pageid; $sql .= " AND rev_id = ". $revisionid; $approvals = 0; $res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {	           $row = $dbr->fetchRow($res); $approvals = $row[0]; }	       $dbr->freeResult($res);

// Check how many must sign it $sql = "SELECT count(*) as signers FROM ". $dbr->tableName('approval_request'); $sql .= " WHERE page_id = ". $pageid; $signers = 0; $res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {	           $row = $dbr->fetchRow($res); $signers = $row[0]; }	       $dbr->freeResult($res); // If all signer already signed, generate a new version (or subversion) if (($signers > 0) && ($approvals == $signers)) {				// Check if page already have a version $sql = "SELECT last_approved_revision_id, version, subversion"; $sql .= " FROM ". $dbr->tableName('approval_version'); $sql .= " WHERE page_id = ". $pageid; $page_version = 0; $page_subversion = 0; $page_last_revision = 0; $res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {		           $row = $dbr->fetchRow($res); $page_last_revision = $row[0]; $page_version = $row[1]; $page_subversion = $row[2]; }		       else {		           $sql =  "INSERT INTO ". $dbr->tableName('approval_version'); $sql .= " (page_id) VALUES"; $sql .= " ($pageid)"; $resInsert = $dbr->query( $sql, __METHOD__ ); }		       $dbr->freeResult($res);

// Check the amount of changes $sql = "SELECT count(*) as edits"; $sql .= " FROM ". $dbr->tableName('revision'); $sql .= " WHERE rev_page = ". $pageid; $sql .= " AND rev_id > ". $page_last_revision; $sql .= " AND rev_minor_edit = 0"; $page_edits = 0; $res = $dbr->query( $sql, __METHOD__ ); if ($dbr->numRows($res)) {		           $row = $dbr->fetchRow($res); $page_edits = $row[0]; }		       $dbr->freeResult($res); // If not all of the changes are minor if ($page_edits > 0) {		       	$page_version++; $page_subversion = 0; }		       else {		       	$page_subversion++; }

// Update version in database $sql = "UPDATE ". $dbr->tableName('approval_version'). " SET"; $sql .= " last_approved_revision_id = $revisionid,"; $sql .= " version = $page_version,"; $sql .= " subversion = $page_subversion"; $sql .= " WHERE page_id = ". $pageid; $res = $dbr->query( $sql, __METHOD__ ); }

// Clear document cache $article->purge; }       else {           $wgOut->addWikiText(wfMsgDA("daPreviouslySigned") . "$signDate."); }

return false; }   else return true; }