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 = 'twiki';
# DB user's password
my $DB_PASS = 'twiki';



# TWiki ihook

sub initPlugin {

    return 1;
}


# TWiki hook; this is run on the text being translated to HTML, and is
# your opportunity to expand any tags in that text.
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;
    #DBI->trace(10);
    unless( $connection ) {
        $connection = DBI->connect( $DB_PATH,
                                    $DB_USER,
                                    $DB_PASS,
                                    {
                                        PrintError => 0,
                                        RaiseError => 1,
                                    });
    }
    return $connection;
}




sub _show_bugs {
   my $args = shift;

   my @headers = map { "*$_*" } qw/ ID Title Priority Name FixFor State /;

   my $fmt = "| %s | %s | %s | %s | %s | %s |\n";

  #my $sql ="SELECT Bug.ixBug, Bug.sTitle, Priority.sPriority, Person.sFullName, FixFor.sFixFor, Status.sStatus FROM Bug, Priority, Person, FixFor, Status WHERE (Bug.ixPriority=Priority.ixPriority) AND (Bug.ixPersonAssignedTo=Person.ixPerson) AND (Bug.ixStatus = Status.ixStatus) AND (Bug.ixFixFor = FixFor.ixFixFor) AND (Bug.ixBug = 3500)";
  # my $sql ="SELECT Bug.ixBug, Bug.sTitle, FROM Bug";
   my $sql ="SELECT Bug.ixBug, Bug.sTitle, Bug.ixBug, Bug.sTitle, Bug.ixBug, Bug.sTitle FROM Bug WHERE Bug.ixBug = $args";


_connect();
my $sth =$connection->prepare($sql);
my @rows = $sth->fetchrow_array;
my @array = $sth->fetchrow_array;

return join '', map {sprintf $fmt, @$_ } \@headers, \@rows;


 $sth->finish;

}


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

1;

##</code><code>##


#!/usr/bin/perl

use strict;

use DBI;

use vars qw( $sth ); # cached connection

my $DB_PATH = "DBI:Sybase:server=192.168.70.56" ;
my $DB_USER = "twiki";
my $DB_PASS = "twiki";

my $dbh = DBI->connect($DB_PATH, $DB_USER, $DB_PASS)|| die "Couldn't connect to database";

print "Database connection established. \n";

my $args=<STDIN>;

my $sth = $dbh->prepare
(
my $sql ="SELECT Bug.ixBug, Bug.sTitle, Priority.sPriority, Person.sFullName, FixFor.sFixFor, Status.sStatus FROM Bug, Priority, Person, FixFor, Status WHERE (Bug.ixPriority=Priority.ixPriority) AND (Bug.ixPersonAssignedTo=Person.ixPerson) AND (Bug.ixStatus = Status.ixStatus) AND (Bug.ixFixFor = FixFor.ixFixFor) AND (Bug.ixBug = $args)");

$sth->execute;

#print out data

#my $headers =
#'| *ID* | *TITLE* | *PRIORITY* | *Assigned To* | *Fix For* | *Status* |';
my $headers = map { "*$_*" } qw/ ID Title Priority Assigned To 'Fix For' State /;
my $fmt = "| %s | %s | %s | %s | %s | %s | %s |\n ";

print "$headers \n";
my @row;
while ( @row = $sth->fetchrow_array() ) {
print "@row \n";
}



$dbh->disconnect;