#
package TWiki::Plugins::FogbugzTablePlugin;

use strict;
use DBI;

use vars qw( $connection ); # cached connection

# DBI specification of the connection to the database
my $DB_PATH = "DBI:Sybase:server=192.168.70.56";
# DB user
my $DB_USER = '';
# DB user's password
my $DB_PASS = '';

# lookup i created
my $indirect = {
    bugstate => { table => 'Status',
                  index => 'ixStatus',
                  string => 'sStatus' },
    bugpri =>  { table => 'Priority',
                 index => 'ixPriority',
                 string => 'sPriority' },
    bugassign => { table => 'Person_1',
                   index => 'ixPerson',
                   string => 'sFullName' }
   };

# TWiki ihook
sub initPlugin {
    return 1;
}


sub commonTagsHandler {
    #my($text, $topic, $web ) = @_;

    # $_[0] refers to the first parameter to this function
    $_[0] =~ s/\bFog(\d+)/_link_to_bug($1)/ge;
    $_[0] =~ s/%FOG{(.*?)}%/_show_bugs($1)/ge;
}

# Connect to the DB
sub _connect {
    my $this = shift;

    unless( $connection ) {
#        DBI->trace(10);
        $connection = DBI->connect( $DB_PATH,
                                    $DB_USER,
                                    $DB_PASS,
                                    {
                                        PrintError => 0,
                                        RaiseError => 1,
                                    });
    }

    return $connection;
}

# Look up a string in another table, as indicated by the indirection
# table in $indirect. If the string isn't indirected, simply return it.
# Note that this could also be done by compiling a smarter query, but in
# most cases this is quite fast enough, and demonstrates the principle
# nicely.
sub _lookup {
    my( $db, $fieldName, $row ) = @_;

    my $indy = $indirect->{$fieldName};
    if( $indy ) {
        my $query = "SELECT $indy->{string} FROM $indy->{table} ".
          "WHERE $indy->{index}=$row->{$fieldName}";
        my $sth = $db->prepare( $query );
        eval { $sth->execute(); };
        die "$query $@" if $@;
        my $row = $sth->fetchrow_hashref();
        return $row->{$indy->{string}};
    } else {
        return $row->{$fieldName};
    }
}



sub _show_bugs {
    my $args = shift;

    my $headers =
      '| *ID* | *Title* | *Priority* | *Assignment* | *State* |';

    my $format =
      '| fog$bugid | $sTitle | $bugpri | $bugassign | $bugstate |';

    my $query = 'SELECT ';
    my $fieldsel = $format;
    my @fieldset;
    while( $fieldsel =~ s/\$(\w+)// ) {
        push( @fieldset, $1 );
    }
    $query .= join(',', @fieldset );
    $query .= " FROM Bug";

    $query .= ' WHERE bugid IN ('.$args.')';

    my $db = _connect();
    my $sth = $db->prepare( $query );
    eval { $sth->execute(); };
    die "$query $@" if $@;

    my $result = $headers;
    my $table = "$headers\n";
    while( my $row = $sth->fetchrow_hashref() ) {
        my $fields = $format;
        $fields =~ s/\$(\w+)/_lookup($db, $1, $row)/ge;
        $table .= "$fields\n";
    }
    return $table;
}

sub _link_to_bug {
    my $bugid = shift;
    return '[[http://apwadev01/fogbugz/default.asp?'.$bugid.'][Fog'.$bugid.']]';
}

1;