%META:TOPICINFO{author="lcampos" date="1087408800" format="1.0" version="1.9"}%
---+ Message Board Plugin

This is a database based message board plugin for the <nop>TWiki system. Users can post and read messages from a database as if it was a message board fixed on the wall.

---++ Syntax Rules

Use the TWiki Tag =%<nop>MESSAGE_BOARD%= with options:

| *Option Name* | *Value Range* | *Description* |
| =displayOnly= | ='0'= or ='1'= | When set to =1= causes Message Board to just display messages, not allowing users to post new messages. When set to =0= will display posted messages and allow users to post new messages. Defaults to =0=. |
| =messageOrder= | ='ASC'=, ='DESC'= | Chooses the order of messages, =ASC= meaning newer messages showing first, =DESC= meaning older messages showing first. Defaults to =ASC=. |

---++ <nop>%TOPIC% Global Settings

	* One line description, shown in the %TWIKIWEB%.TextFormattingRules topic:
		* Set SHORTDESCRIPTION = Database-Based message board plugin.

	* Color Settings
		* Set TABLE_HEAD_COLOR = %WEBCOLOR%

	* Icons and Image Settings
		* Set TRASH_CAN_ICON_LINK = <img src="%PUBURL%/%TWIKIWEB%/MessageBoardPlugin/trashcan.png" alt="Remove" border="0" />
		* Set PENCIL_ICON_LINK = <img src="%PUBURL%/%TWIKIWEB%/MessageBoardPlugin/editicon.png" alt="Edit" border="0" />

	* Database Settings
		* Set DB_DRIVER = mysql
		* Set DB_SERVER = _IP or hostname here_
		* Set DB_SERVER_PORT = 3306
		* Set DB_DATABASE = message_board
		* Set DB_TABLE = message
		* Set DB_USER = board
		* Set DB_PASSWORD = _password_

	* Plugin Messages (so you can internationalize them):
	* Error opening connection to the database
		* Set MSG_DB_CONNECT_ERROR = %X% %RED% Error connecting to the database%ENDCOLOR%
	* Error closing connection to the database
		* Set MSG_DB_CLOSE_ERROR = %X% %RED% Error closing the database connection%ENDCOLOR%
	* Error during query preparation
		* Set MSG_DB_PREPARE_ERROR = %X% %RED% Error preparing query%ENDCOLOR%
	* Error during data fetch
		* Set MSG_DB_FETCH_ERROR = %X% %RED% Error fetching data from database%ENDCOLOR%
	* Error during query execution
		* Set MSG_DB_EXECUTE_ERROR = %X% %RED% Error executing query%ENDCOLOR%
	* No data found error
		* Set MSG_DB_NO_DATA_ERROR = %X% %RED% No data found%ENDCOLOR%
	* Update error message
		* Set MSG_DB_UPDATE_ERROR = %X% %RED% Error updating data%ENDCOLOR%
	* Insert error message 
		* Set MSG_DB_INSERT_ERROR = %X% %RED% Error inserting data%ENDCOLOR%

	* Debug plugin: (See output in =data/debug.txt=)
		* Set DEBUG = 0

---++ Plugin Installation Instructions

__Note:__ You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the plugin on the server where TWiki is running.

	* Download the ZIP file from the Plugin web (see below)
	* Unzip ==%TOPIC%.zip== in your twiki installation directory. Content:
	  | *File:* | *Description:* |
	  | ==data/TWiki/%TOPIC%.txt== | Plugin topic |
	  | ==data/TWiki/%TOPIC%.txt,v== | Plugin topic repository |
	  | ==lib/TWiki/Plugins/%TOPIC%.pm== | Plugin Perl module |
	  | ==pub/TWiki/MessageBoardPlugin/trashcan.png== | Trash Can Icon |
	  | ==pub/TWiki/MessageBoardPlugin/editicon.png== | Pencil and Paper Icon |
	* Create a table into your database using (this is mysql SQL, the only DB supported at the moment)
<pre>
         CREATE DATABASE message_board;

	 CREATE TABLE message(
		id INT PRIMARY KEY AUTO_INCREMENT,
		author VARCHAR(50) NOT NULL,
		due DATETIME NOT NULL,
		posted DATETIME NOT NULL,
		msg TEXT NOT NULL,
		dropped ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N'
	 )
</pre>
	* Create a new user and give it appropriated permissions:
<pre>
		GRANT SELECT, INSERT, UPDATE, DELETE
		  ON messsage_board.message
		  TO board IDENTIFIED BY 'b0aRd'
</pre>
	* Test if the plugin is correctly installed:
		* Open a Sandbox.MessageBoardTestArea and insert <code>%<nop>MESSAGE_BOARD{ messageOrder="DESC" }%
##</code><code>##

# Plugin for TWiki Collaboration Platform, http://TWiki.org/
#
# Copyright (C) 2004 Luis Campos de Carvalho, monsieur_champs@yahoo.com.br
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details, published at 
# http://www.gnu.org/copyleft/gpl.html
#

# =========================
package TWiki::Plugins::MessageBoardPlugin;
use strict;
use warnings;
use DBI;

# =========================
use vars qw(
        $web $topic $user $installWeb $VERSION $pluginName
        $debug %db %i18nMessage %color
    );

$VERSION = '1.000';
$pluginName = 'MessageBoardPlugin';

# =========================
sub initPlugin
{
    ( $topic, $web, $user, $installWeb ) = @_;

    # check for Plugins.pm versions
    if( $TWiki::Plugins::VERSION < 1 ) {
        TWiki::Func::writeWarning( "Version mismatch between $pluginName and Plugins.pm" );
        return 0;
    }

    # Get plugin debug flag
    $debug = TWiki::Func::getPreferencesFlag( "\U$pluginName\E_DEBUG" );

    # Get plugin color settings
    %color =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
	( [ TABLE_HEAD   => "\U$pluginName\E_TABLE_HEAD_COLOR" ],
	  [ REVERSE_LINE => "\U$pluginName\E_REVERSE_LINE_COLOR" ],
	);

    # Get plugin messages (so you can internationalize and customize
    # them at will )
    %i18nMessage =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
	( [ DB_CONNECT_ERROR => "\U$pluginName\E_MSG_DB_CONNECT_ERROR" ],
	  [ DB_CLOSE_ERROR   => "\U$pluginName\E_MSG_DB_CLOSE_ERROR" ],
	  [ DB_PREPARE_ERROR => "\U$pluginName\E_MSG_DB_PREPARE_ERROR" ],
	  [ DB_FETCH_ERROR   => "\U$pluginName\E_MSG_DB_FETCH_ERROR" ],
	  [ DB_EXECUTE_ERROR => "\U$pluginName\E_MSG_DB_EXECUTE_ERROR" ],
	  [ DB_NO_DATA_ERROR => "\U$pluginName\E_MSG_DB_NO_DATA_ERROR" ],
	  [ DB_UPDATE_ERROR => "\U$pluginName\E_MSG_DB_UPDATE_ERROR" ],
	  [ DB_INSERT_ERROR => "\U$pluginName\E_MSG_DB_INSERT_ERROR" ],
	);

    # Get plugin database meta-data
    %db =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
	( [ driver   => "\U$pluginName\E_DB_DRIVER" ],
	  [ host     => "\U$pluginName\E_DB_SERVER" ],
	  [ port     => "\U$pluginName\E_DB_SERVER_PORT" ],
	  [ database => "\U$pluginName\E_DB_DATABASE" ],
	  [ table    => "\U$pluginName\E_DB_TABLE" ],
	  [ user     => "\U$pluginName\E_DB_USER" ],
	  [ passwd   => "\U$pluginName\E_DB_PASSWORD" ],
	);

    $db{dbh} = eval{
      DBI->connect( 'dbi:'.$db{driver}.
		    ':database='.$db{database}.
		    ';hostname='.$db{host}.
		    ';port='.$db{port},
		    $db{user},
		    $db{passwd},
		    { RaiseError => 1, PrintError => 0 } )
    };
    $db{connection_error} = $@ if $@;

    # Plugin correctly initialized
    # TWiki::Func::writeDebug( "- TWiki::Plugins::${pluginName}::initPlugin( $web.$topic ) is OK" ) if $debug;
    return 1;
}

# =========================
sub commonTagsHandler{
  ### my ( $text, $topic, $web ) = @_;
  # do not uncomment, use $_[0], $_[1]... instead

  # TWiki::Func::writeDebug("- ${pluginName}::commonTagsHandler( $_[2].$_[1] )") if $debug;

  # This is the place to define customized tags and variables
  # Called by sub handleCommonTags, after %INCLUDE:"..."%

  $_[0] =~ s/(%MESSAGE_BOARD(?:{[^}]+})?%)/&board($1)/ge;
}

# =========================
sub display{
  my $msgOrder = shift;

  my $sth = eval {
    $db{dbh}->prepare( q{ SELECT id
                               , author
                               , DATE_FORMAT( due, '%T<br />%d/%m/%Y' ) as due
                               , DATE_FORMAT( posted, '%T<br />%d/%m/%Y' ) as posted
                               , msg
			  FROM message
			  WHERE dropped = 'N'
			  AND due >= NOW()
			  ORDER BY id
			} . uc $msgOrder
		     );
  };
  return $i18nMessage{DB_PREPARE_ERROR}. ": $@." if $@;
  eval { $sth->execute; };
  return $i18nMessage{DB_EXECUTE_ERROR}. ": $@." if $@;
  my $result = eval { $sth->fetchall_arrayref( {} ); };
  return $i18nMessage{DB_FETCH_ERROR}. ": $@." if $@;

  my $trash_icon = &TWiki::Func::expandCommonVariables( &TWiki::Prefs::getPreferencesValue( "\U$pluginName\E_TRASH_CAN_ICON_LINK" ), $topic, $web );
  my $pencil_icon = &TWiki::Func::expandCommonVariables( &TWiki::Prefs::getPreferencesValue( "\U$pluginName\E_PENCIL_ICON_LINK" ), $topic, $web );

  # Build Message Board Table and return it.
  return
    qq{<table align="center" border="1" cellpadding="5" cellspacing="0">
  <tr valign="middle" align="center" bgcolor="$color{TABLE_HEAD}">
    <td> <b> Due&nbsp;Date </b> </td>
    <td> <b> Posted&nbsp;Date </b> </td>
    <td> <b> Author </b> </td>
    <td> <b> Drop &amp;<br />Change </b> </td>
    <td> <b> Message </b> </td>
  </tr> } .
    ( $result ? join "\n",
      map( '<tr valign="top" align="center"><td>' .
	   $_->{due} . '</td><td>' . $_->{posted} . '</td><td> ' .
	   $_->{author} . ' </td><td><a href="' .
	   &TWiki::Func::getViewUrl( $web, $topic ) . '?message_id=' .
	   $_->{id} . ';action=edit">' . $pencil_icon .
	   '</a><a href="' . &TWiki::Func::getViewUrl( $web, $topic ) .
	   '?message_id=' . $_->{id} . ';action=remove">' . $trash_icon .
	   '</a></td><td align="left">' . $_->{msg} . '</td></tr>',
	   @$result
	 )
      : $i18nMessage{DB_NO_DATA_ERROR}
    ) . '</table>';
}

sub inputBox{
  # Don't uncomment, use @_ instead
  #   my $action_url = shift; # $_[0]
  #   my $msgid      = shift; # $_[1]
  #   my $action     = shift; # $_[2]
  return
qq{<form action="$_[0]" method="post">\n}.
($_[1]? qq{  <input type="hidden" name="message_id" value="$_[1]" />\n}:'').
qq{  <input type="hidden" name="action" value="$_[2]" />
  <input type="hidden" name="commit" value="1" />
  <table align="center" border="1" cellpadding="5" cellspacing="0">
    <tr valign="middle" align="center" bgcolor="$color{TABLE_HEAD}">
      <td colspan="2"> <b>}
  .($_[2] eq 'edit'? "Edit Message #$_[1]" : 'Compose New Message' ).
  qq{ </b> </td>
    </tr>
    <tr valign="middle" align="left" >
      <td> Author: </td>
      <td>
}.( $_[2] eq 'edit'? q{        <input type="text" name="author" size="40" }.
  ( $_[3]? qq{value="$_[3]->{author}"} : &TWiki::Func::getWikiName() )
 . qq{ />} : &TWiki::Func::getWikiName() ) . q{
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>Due Date: </td>
      <td>} . &gen_date_selector( 'due', ( $_[3]? @{$_[3]}{'day','month','year','hour','minute'} : (localtime)[3], (localtime)[4]+1, (localtime)[5]+1900, 23, 59 ) ) . qq{
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>Message: </td>
      <td>
        <textarea rows="5" name="msg" cols="50">}.($_[3]? $_[3]->{msg} : '' ).qq{</textarea>
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>
        <input type="checkbox" name="dropped" value="Y" }.($_[3] && $_[3]->{dropped} eq 'Y'? 'CHECKED':'' ).q{>&nbsp;Dropped.
      </td>
      <td align="right">
        } . ( $_[2] eq 'edit' ? qq{<a href="$_[0]">Cancel Edition</a> &nbsp;&nbsp;&nbsp; } : '' ) . qq{<input type="submit" name="change" value="}. ( $_[2] eq 'edit' ? 'Change' : 'Post' ) . q{ Message">
      </td>
    </tr>
  </table>
</form>
};
}

# =========================
sub board{
  # Deal with database errors
  if( ! $db{dbh} && $db{connection_error} ){
    return $i18nMessage{DB_CONNECT_ERROR} . ": " . $db{connection_error};
  }

  # Recover tag, extract parameters
  # my $tag = shift;
  my( $displayOnly, $messageOrder ) =
    ( &TWiki::Func::extractNameValuePair( $_[0], 'displayOnly' ),
      &TWiki::Func::extractNameValuePair( $_[0], 'messageOrder' ) || 'ASC' );

  my $q = &TWiki::Func::getCgiQuery();

  my $msgid = $q->param( 'message_id' );
  my $action = $q->param( 'action' );

  if( $action eq 'remove' ){
    ##################################################
    # Remove action
    ##################################################
    eval{
      $db{dbh}->do( q{ UPDATE message
		       SET dropped = 'Y'
		       WHERE id = ? },
		    { RaiseError => 1 },
		    $msgid
		  )
    };
    return $i18nMessage{DB_EXECUTE_ERROR} . ': ' . $db{dbh}->errstr if $@;

  }elsif( $action eq 'edit' ){
    ##################################################
    # Edit Action
    ##################################################
    if( $q->param( 'commit' ) ){
      ##################################################
      # Commit previous started edition
      ##################################################
      eval{ $db{dbh}->do( # DBI::do( $sql, \%attr, @bind )
			 q{UPDATE message
			   SET author  = ?
			     , due     = ?
			     , msg     = ?
			     , dropped = ?
			   WHERE id = ?},
			 { RaiseError => 1 },
			 $q->param( 'author' ),
			 sprintf( '%04d-%02d-%02d %02d:%02d:00',
				  map( $q->param( $_ ),
				       qw( due_year due_month due_day
					   due_hour due_minute )
				     )
				),
			 $q->param( 'msg' ),
			 $q->param( 'dropped' )? 'Y' : 'N',
			 $msgid
			)
	  };
      return $i18nMessage{DB_UPDATE_ERROR}.': '.$@ if $@;
    }else{
      ##################################################
      # Start new edition
      ##################################################
      my $data;
      # recover data from existing record...
      eval{
	my $sth = $db{dbh}->prepare( # DBI::prepare( $sql )
				    q{SELECT author
				           , EXTRACT(DAY FROM due) AS day
				           , EXTRACT(MONTH FROM due) AS month
				           , EXTRACT(YEAR FROM due) AS year
				           , EXTRACT(HOUR FROM due) AS hour
				           , EXTRACT(MINUTE FROM due) AS minute
				           , msg
				           , dropped
				      FROM message
				      WHERE id = ?}
				   );
	my $result = $sth->execute( $msgid ); # DBI::execute( @bind )
	$data = $sth->fetchrow_hashref();
      };
      return $i18nMessage{DB_FETCH_ERROR} . ': ' . $@ if $@;

      # Build HTML
      return &inputBox( &TWiki::Func::getViewUrl( $web, $topic ),
			$msgid, 'edit', $data );
    }
  }elsif( $action eq 'new' ){
    ##################################################
    # Post New Message
    ##################################################
    if( $q->param( 'commit' ) ){
      ##################################################
      # Commit previous started edition
      ##################################################
      eval{
	$db{dbh}->do( # DBI::do( $sql, \%attr, @bind )
		     q{INSERT INTO message( author, due, posted, msg, dropped )
		       VALUES( ?, ?, NOW(), ?, ? ) },
		     { RaiseError => 1 },
		     &TWiki::Func::getWikiName(),
		     sprintf( '%04d-%02d-%02d %02d:%02d:00',
			      map( $q->param( $_ ),
				   qw( due_year due_month due_day
				       due_hour due_minute )
				 )
			    ),
		     $q->param( 'msg' ),
		     $q->param( 'dropped' )? 'Y' : 'N',
		    )
      };
      return $i18nMessage{DB_INSERT_ERROR}.': '.$@ if $@;
    }
  }

  ##################################################
  # Execute display action
  ##################################################
  return '<table align="center" border="0" cellpadding="0" cellspacing="2">'.
    '<tr align="center">'.
      '<td>'.&display( $messageOrder ).'</td></tr>'.
	( $displayOnly? '' : '<tr align="center"><td>' . &inputBox( &TWiki::Func::getViewUrl( $web, $topic ), undef, 'new', undef ) . '</td></tr>' ).
	  '</table>';
}

sub gen_date_selector{
  # my $name = shift;
  # # Those are for the 'selected' HTML tag:
  # my $ref_day = shift;    # numeric current day (1..31)
  # my $ref_month = shift;  # numeric current month (1..12)
  # my $ref_year = shift;   # numeric current year (0..MAX_BIGINT)
  # my $ref_hour = shift;   # numeric current hour (0..23)
  # my $ref_minute = shift; # numeric current minute (0..59)
  join '',
    ( qq{<select name="$_[0]_day">\n },
      (
       # generate day 'option' tags
       map {
	 my $d = sprintf '%02d', $_;
	 qq'<option value="$d"' .
	   ( $d == $_[1] ? ' selected' : '') .
	     ">$d</option>\n";
       } 1..31
      ),
      qq{ </select> / <select name="$_[0]_month">\n},
      (
       # generate month 'option' tags
       map {
	 my $m = sprintf '%02d', $_;
	 qq'<option value="$m"' .
	   ( $m == $_[2] ? ' selected' : '' ) .
	     ">$m</option>\n";
       } 1..12
      ),
      qq{ </select> / <select name="$_[0]_year">\n},
      (
       # generate year 'option' tags
       map {
	 my $y = sprintf '%04d', $_;
	 qq'<option value="$y"' .
	   ( $y == $_[3] ? ' selected' : '' ) .
	     ">$y</option>\n";
       } ( (localtime)[5]+1900 ) .. ( (localtime)[5]+1902 )
      ),
      qq{</select>&nbsp;<select name="$_[0]_hour">},
      (
       # generate hour 'option' tags
       map{
	 my $h = sprintf '%02d', $_;
	 qq'<option value="$h"' .
	   ( $h == $_[4] ? ' selected' : '' ) .
	     ">$h</option>\n";
       } 0..23
      ),
      qq{</select>:<select name="$_[0]_minute">},
      (
       # generate minute 'option' tags
       map{
	 my $m = sprintf '%02d', $_;
	 qq'<option value="$m"' .
	   ( $m == $_[5] ? ' selected' : '' ) .
	     ">$m</option>\n";
       } 0..59
      ),
      qq{</select>:00},
    );
}
1;